|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 11th, 2005, 04:01 AM
|
|||
|
|||
Handle NULL return values in Stored Procedure?
I've been confounded with a problem. Cdnt find an answer.
I'm trying to write a stored procedure in SQL Server 2000 and using a cursor, I do a loop as: Code:
SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select DISTINCT id from tbl_Items ORDER BY id
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @A
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @q1=SUM(OI.QTY)
FROM tbl_OrderItems OI, tbl_DeliveryOrder DO
WHERE.........
.
.
.
SELECT @q2=SUM(OI.QTY)
FROM tbl_OrderItems OI, tbl_DeliveryOrder DO
WHERE........
I do 2 similar SELECT statements within this loop. Finally at the end of each loop cycle, I need to add the @q1 and @q2 variables and insert it in another table. Here's where my problem arises: If any SELECT statement does not return a value, @q1 or @q2 will contain NULL right? If so, how can I add @q1 and @q2? I get the following values for @q1 and @q2 when run independently. @q1 @q2 100 NULL 200 25 100 NULL Before adding I tried the line: Code:
IF @q1 = NULL set @q1 = 0 but that doesn't seem to help too. Whereever one of the variables contain no value (i.e. the SELECT returns nothing), I cannot get that added up sum. I only get a result when both variables have values (from the SELECT statement) SOMEBDY HELP ME!!
|
|
#2
May 11th, 2005, 07:52 AM
|
|||
|
|||
|
You might try
Code:
SELECT @q1 = ISNULL(SUM(O1.Qty), 0) . . . I'm not sure I see the need for a cursor here. Summing operations seldom require a cursor when the summing is confined to one table unless the test parameter changes based on a value in a related table, and even then it is rarely needed. Hope this helps. Last edited by trubolotta : May 11th, 2005 at 07:53 AM. Reason: code fix
|
|
#3
May 11th, 2005, 09:37 PM
|
|||
|
|||
|
Oh yes thnkx. It worked!
Didnt knw abt the ISNULL function.  btw, I need the cursor here coz I need @q1 and @q2 from 2 SQL statements based on the cursor value (tbl_Items.id). I later add @q1 and @q2
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Stored Procedure! |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
