Null Values in Stored Procedures

[jjrharper]

Hi,

I am calculating two fields (qtyorder-qtydesp) in a stored procedure.

The second field is from another query (view) which may not have a record relating to the main record (stored procedure).

The problem is the second field is simply left blank but I need it to be 0.

It makes the calculation in turn result in a blank field, i.e. 1-0 should equal 1 but because of the blank field it returns a null value.

I found that the good old Nz function wont work with stored procedures, is there any alternative?

Thanks,
James

 

[dan-cat]

are you working in SQL server? I'm assuming yes.

You need to use the CASE statement

SELECT CASE WHEN myField IS NULL THEN 0 ELSE myField END as myFieldName
FROM myTable

 

[jjrharper]

Thanks for your help, I just managed to work it out using a mix of COALESCE and SUM.

This is the first time I have actually used SQL and Access 2003 together, its a bit different from Access 97! But I have managed to get a stock & CRM system together in the last couple of months!

James

 

[FoFa]

ISNULL function works the same as NZ
ISNULL(mycol,0) would return 0 if mycol is null, or the value of mycol if it is not.

 

[dan-cat]

ISNULL is a much more efficient way of doing it

 

[jjrharper]

Thanks, I shall look into using ISNULL instead of the my current method!
You have both been helpful!

James