Stored Procedure - replace null with text msg

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure - replace null with text msg

Author

Topic

mwoolgar
Starting Member

11 Posts

Posted - 2006-01-27 : 09:46:31

Hi

I'm trying to create a stored procedure using the northwind db which will do the following:

SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock
FROM Products

However, where UnitsInStocks = 0 I would like the words "Sorry, out of stock" to appear. I will then call this from an ASP page.

Can anyone help please?

Cheers

Woolly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 09:50:54

I think it is better to handle it in your ASP page by checking the value of UnitsInStock
If you want to do this from query, then you need to convert UnitsInStock into varchar

Madhivanan

Failing to plan is Planning to fail

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-01-27 : 10:01:38

SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock = Case when UnitsInStock = 0
then "Sorry, out of stock"
Else Cast(UnitsInStock as Varchar(20))
end
FROM Products

Also Be aware 0 and Null are not the same!!!!

Jim
Users <> Logic

mwoolgar
Starting Member

11 Posts

Posted - 2006-01-27 : 10:05:47

I agree but I failed to mention I'm using asp.net 2.0 and I'm trying to call this in a GridView. I know how to do the change in normal asp but not in .net.

A colleague suggested the following -

SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock FROM Products where UnitsInStock > 0
UNION
SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, ‘sorry no stock’ FROM Products Where UnitsInStock = 0

.... convert the units in stock to a string type then change the value ‘0’ to 'sorry no stock'
As a quick guess maybe you can wrap the replace around the convert so try :

Replace(Convert(varchar(50),UnitsInStock ),’0’, 'sorry no stock)'

Woolly

mwoolgar
Starting Member

11 Posts

Posted - 2006-01-27 : 10:24:52

Thanks Jim ... but I get an error with this...

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Sorry, out of stock'.

quote:
Originally posted by JimL

SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock = Case when UnitsInStock = 0
then "Sorry, out of stock"
Else Cast(UnitsInStock as Varchar(20))
end
FROM Products

Also Be aware 0 and Null are not the same!!!!

Jim
Users <> Logic

Woolly

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2006-01-27 : 10:30:50

use single quotes

then 'Sorry, out of stock'

mwoolgar
Starting Member

11 Posts

Posted - 2006-01-27 : 10:36:19

Hey... well done that did the trick

Thanks

Woolly