Why will Stored Procedure isNull not work?

[MnM]

I have a stored procedure and if the value from the SQL statement is null I want to to be zero. However, if the value is null it is passed back my my ASP page as null and not as 0. My code is below, I was wondering if you guys could take a look at it and tell me if I did something wrong. Thanks!

CREATE PROCEDURE sp_Inventory_Valuation
@reportID smallint,
@storenum tinyint,
@InvValue int OUTPUT,
@Sku int OUTPUT
AS
SELECT @InvValue=isNull(Merch,0), @Sku=isNull(SkuCount,0) from SalesAnalysis where reportID_MO = @reportID and PCnum = @storenum

 

[AshleyRickards]

Try adding in a CASE statement like:

CASE when NULL then '0' Ashley L Rickards
SQL DBA

 

[cmmrfrds]

The isnull will work if the select returns a record but if no records are found with the select then isnull won't be activated since there isn't any data to work with. Make sure you are returning records.

 

[zhupeng]

Since you didn't set default value for these two output variables, so SQL Server use its default is Null. If set your each variable at beginning, then you should be OK.

good luck!

CREATE PROCEDURE sp_Inventory_Valuation
@reportID smallint,
@storenum tinyint,
@InvValue int OUTPUT,
@Sku int OUTPUT
AS

set @InvValue = 0,
set @Sku =0

SELECT @InvValue=isNull(Merch,0), @Sku=isNull(SkuCount,0) from SalesAnalysis where reportID_MO = @reportID and PCnum = @storenum

 

[MnM]

Thanks for all of the responses!

cmmrfrds - you made me realize that one of the items I was doing a query on wasn't returning any records! Thanks!

zhupeng - I took your advice and set each OUTPUT variable to 0 at the beginning and if no record was found is was still 0, not null, otherwise it was set the the data from from query! Thanks!

Once again Thank You, Thank You, Thank You!