return value of empty list
Hi,
I'm trying check the records which satisfies the following condn.
select @p1 = su1 from RER where (no=10 and atp ='N')
It returns empty list . bcos no match found.that's ok.
But i need to write the logic if record is not there.
i tried to do as follows
if ((@p1 =null) or (p1=' '))
...some logic
else
.. other logic
It's always executing else case. How do i catch empty value using sql query.
Is there any command there like null?
I tried in SQL query analyzer. Using SQL 2000.
Thank You
[585 byte] By [
ar_pad] at [2007-12-23]
ISNULL
Replaces NULL with the specified replacement value.
Syntax
ISNULL ( check_expression , replacement_value )
Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
Return Types
Returns the same type as check_expression.
Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.
Examples
A. Use ISNULL with AVG
This example finds the average of the prices of all titles, substituting the value $10.00 for all NULL entries in the price column of the titles table.
USE pubsGO
SELECT AVG(ISNULL(price, $10.00))
FROM titles
GO
Here is the result set:
-- 14.24
(1 row(s) affected)
B. Use ISNULL
This example selects the title, type, and price for all books in the titles table. If the price for a given title is NULL, the price shown in the result set is 0.00.
USE pubsGO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type,
ISNULL(price, 0.00) AS Price
FROM titles
GO
Here is the result set:
Title Type Price --
The Busy Execut business 19.99
Cooking with Co business 11.95
You Can Combat business 2.99
Straight Talk A business 19.99
Silicon Valley mod_cook 19.99
The Gourmet Mic mod_cook 2.99
The Psychology UNDECIDED 0.00
But Is It User popular_comp 22.95
Secrets of Sili popular_comp 20.00
Net Etiquette popular_comp 0.00
Computer Phobic psychology 21.59
Is Anger the En psychology 10.95
Life Without Fe psychology 7.00
Prolonged Data psychology 19.99
Emotional Secur psychology 7.99
Onions, Leeks, trad_cook 20.95
Fifty Years in trad_cook 11.95
Sushi, Anyone? trad_cook 14.99
Hi
1. Try to catch number of records effected straight after your assinment and use that count to decide on actions
2. And a second thought : your select @p1= will always return no more then 1 record even if more records exist . Just wanted to make sure you are aware of that.
DECLARE @iCount INT
SELECT @p1 = su1 from RER where (no=10 and atp ='N')
SELECT @iCount = @@ROWCOUNT
Then you can apply your logic :
IF @iCount > 0
BEGIN
<....>
END
else
BEGIN
<....>
END
NB
ar_pad wrote: