returning value '0' if no rows are returned? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

returning value '0' if no rows are returned?

Author

Topic

indieman
Starting Member

12 Posts

Posted - 2006-04-13 : 14:07:47

hi,

i want to query a table to get the value of a row, but if the row doesnt exist i want it to return the value '0'

is this possible, if so how?

the possible values it can return are 1,2,3 , but if the row doesnt exist i need to return a 0.

how would i go about creating a stored procedure to do this? the following returns the value, but not a 0 if there are no rows.

any help is appreciated,
Ben

create proc sp_getconsultantability @consultantid numeric(9) @module nvarchar(50)
as

select consultantability
from consultant
where consultantid = consultantid
AND module = @module

go

indieman
Starting Member

12 Posts

Posted - 2006-04-13 : 14:18:23

found out the solution. thanks for looking tho!

create proc sp_getconsultantability @consultantid numeric(9) @module nvarchar(50)
as

if exists ( select consultantability from dbo.consultant where consultantid = consultantid AND module = @module )
BEGIN
select consultantability
from dbo.consultant
where consultantid = consultantid
AND module = @module
END
ELSE
BEGIN
SELECT 0 AS consultantability
END
go

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-04-13 : 14:31:10

While that would work, you also end up hitting your table 2 times for the same data. You can accomplish the same thing using a temporary variable inside the procedure and only require a single hit against your table to speed things up: [CODE]declare @Value int
select @Value = consultantability from dbo.consultant where consultantid = consultantid AND module = @module

if @Value is null
select 0 as consultantability
else
select @Value as consultantability[/CODE]

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-04-13 : 14:31:48

This should also work


create proc sp_getconsultantability @consultantid numeric(9) @module nvarchar(50)
as

Select 
	consultantAbility = isnull((select consultantability from dbo.consultant where consultantid = consultantid AND module = @module),0)

Go

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-13 : 14:33:15

Or

declare @consultantability varchar(100)

Select @consultantability = consultantability
from dbo.consultant
where consultantid = consultantid AND module = @module

Select isnull(@consultantability ,0)

Srinika

indieman
Starting Member

12 Posts

Posted - 2006-04-13 : 18:50:09

cheers everyone. its a small university project, a proof of concept for a business, so as i have already implemented the first suggestion i will leave it as is.

thanks,
ben

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources