Returning NULL within Stored Procedure

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

SQL Server Development (2000)

Returning NULL within Stored Procedure

Author

Topic

sbushway
Starting Member

18 Posts

Posted - 2003-11-13 : 12:01:41

Hi,
I have a VB.NET page that calls a stored procedure that looks like this:

CREATE Procdure SPCheckReference
{
@OrderID numeric,
@Reference char(1) OUTPUT
}
AS

SELECT Reference
FROM ordertblx
WHERE OrderID = @OrderID

GO

My problem is that the values of 'Reference' are NULL, and my .NET page throws an error when it tries to set the result of Reference to a variable. What I want to do is, within my stored procedure, set the value of Reference equal to 'X' if the value is NULL. I'm assuming putting an 'IF/ELSE' statement in the stored procedure would work, but I'm not sure how I would set Reference equal to 'X' within that statement. Would it be something like this?

CREATE Procdure SPCheckReference
{
@OrderID numeric,
@Reference char(1) OUTPUT
}
AS

SELECT Reference
FROM ordertblx
WHERE OrderID = @OrderID

IF Reference = NULL
THEN Reference = 'X'
END IF
GO

The code above said that there was an error before the word 'THEN'. Is the IF statement not supposed to go there?

Any help would be appreciated. Thank you in advance.

Suzanne

Granick
Starting Member

46 Posts

Posted - 2003-11-13 : 12:12:29

The easiest way to do conditional replacement of NULL values in a select, in a stored proc or otherwise, is to use ISNULL().

Your proc would look like the following:

CREATE Procdure SPCheckReference
{
@OrderID numeric,
@Reference char(1) OUTPUT
}
AS

SELECT ISNULL(Reference, 'X') As Reference
FROM ordertblx
WHERE OrderID = @OrderID

You can of course replace the 'X' with whatever value you want as a default.

Hope that helps,

Shannon

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-13 : 12:20:31

Isn't there a way to check in .vb.nrt if it's null before you set the variable?

I think that's how it's generally handled....

Brett

8-)

sbushway
Starting Member

18 Posts

Posted - 2003-11-13 : 12:46:58

Hi Shannon,
Thanks for your help - that works great! Well, it works in SQL Query Analyzer just fine (it returns an X if the value is NULL), but I still get an error on my VB.NET page about how it's unable to set a NULL value equal to a string value.

I guess I should ask a .NET forum about this???

Thanks again for your help - I really appreciate it! :)

~Suzanne

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-13 : 13:07:08

quote:

CREATE Procdure SPCheckReference
{
@OrderID numeric,
@Reference char(1) OUTPUT
}
AS

SELECT ISNULL(Reference, 'X') As Reference
FROM ordertblx
WHERE OrderID = @OrderID

That's almost right, but if there are NO rows returned, you will still get null. Also, you are never actually setting the output variable @Refernce to anything. You actually want something like this:

SET @Reference = ISNULL((Select reference from ordertblx where OrderID = @OrderID),'x')

that should do the trick.

- Jeff

Granick
Starting Member

46 Posts

Posted - 2003-11-13 : 13:29:28

Whoops, my bad. That is what I get for reading the select portion of the code and not paying attention to the create procedure portion of the code. I guess that is what I get for not having my caffeine yet.

Shannon

sbushway
Starting Member

18 Posts

Posted - 2003-11-13 : 13:30:40

Jeff,
Perfect! Worked like a charm :)

Thanks a bunch,
Suzanne

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources