returning value ''0'' if no rows are returned?
-
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
SELECTconsultantability
FROM consultant
WHERE consultantid = @consultantid
AND module = @module
go
-
Most common
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
-
thankyou for the quick response! it works a treat
ben
-
There's another, simpler way:
SELECT CASE WHEN consultantability IS NULL
THEN 0
ELSE consultantability
END as consultantability
FROM consultant
WHERE consultantid = @consultantid
AND module = @module
-
Again he stated if there is "no row" he wants a 0. Appologize but this will not work.
-
Hi Ben,
You could use SQL Server System function @@ROWCOUNT which return the number of rows affected by the last statement. At the end of procedure write return statement (Return @@ROWCOUNT) and get that value at calling point.
Regards,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply