March 6, 2009 at 3:31 pm
I have been writing queries for some time, just not well enough as I have learned.
I can handle a null field value:
Select isNull(fName, 'N/A') from t_Names where id = 1234 (if fName is NULL then it is now N/A, but what if there is no record for 1234?)
But I have a query with in a Sproc (for building an email), that returns no record and it is disrupting my process. The best I can explain is I am building an HTML string, for a purchase notification. As part of it I want to find other places of interest in the area of the purchase. I am building a value lets call it @Body varchar(8000). When it gets to the point of crash, it has a value (ie " blah blah") if there is nobody within my specs of the query, @Body = nothing. I know I can do a select statement and then do @@rowcount and check if it did indeed return a row. I just am wondering if there is sytax for null row as there is null field.
Any thoughts?
March 8, 2009 at 2:00 pm
I know of no syntax for a "null row". Either one or more rows are returned, or they aren't, and @@Rowcount is the way to test that. When our UI people insist on having at least one row returned from a stored procedure, I often populate a temporary table and then test @@rowcount. If it is zero, I run another INSERT with constant values to indicate a no-hit, then return the contents of the temporary table.
You can force a row to be returned from a query with a UNION all like this, and I can imagine other ways, but it's also going to run slower than a single query. If your procedure is only returning a single row, you could add a row to your results by doing a UNION ALL in a CTE, then do a SELECT TOP 1 from the CTE.
If anyone else has better ways to handle this issue, I'd like to know about them too. Please let me know if this helps.
---------------------------------------------------------------------------
-- temp TABLE example
---------------------------------------------------------------------------
DECLARE @searchID int
DECLARE @sample TABLE (ID int identity (1,1) primary key, blah varchar(max))
INSERT INTO @sample
SELECT'Apple' UNION ALL
SELECT'Banana' UNION ALL
SELECT'Cantaloupe'
SELECT * FROM @sample
SET @searchID = 99
SELECT ID+0 AS ID, blah -- add zero to not carry over Identity attribute of ID column
INTO #temp
FROM @sample
WHERE ID = @searchID
if @@rowcount = 0 INSERT INTO #temp SELECT @searchID AS ID, '*Not Found' AS blah
SELECT * FROM #temp
DROP TABLE #temp
GO
---------------------------------------------------------------------------
-- force row example
---------------------------------------------------------------------------
DECLARE @searchID int
DECLARE @sample TABLE (ID int identity (1,1) primary key, blah varchar(max))
INSERT INTO @sample
SELECT'Apple' UNION ALL
SELECT'Banana' UNION ALL
SELECT'Cantaloupe'
SELECT * FROM @sample
SET @searchID = 99
--- force row return
;WITH results AS
(SELECT* FROM @sample
WHERE ID = @searchID
)
SELECT *
FROM results UNION ALL
SELECT @searchID, '*Not Found' AS blah
WHERE (SELECT count(*) FROM results) = 0
GO
------------------------------------------------------------------------------
-- TOP 1 example
------------------------------------------------------------------------------
DECLARE @searchID int
DECLARE @sample TABLE (ID int identity (1,1) primary key, blah varchar(max))
INSERT INTO @sample
SELECT 'Apple' UNION ALL
SELECT 'Banana' UNION ALL
SELECT 'Cantaloupe'
SELECT * FROM @sample
SET @searchID = 99
--- force row return
;WITH results AS
(SELECT * FROM @sample
WHERE ID = @searchID
UNION ALL
SELECT @searchID, '*Not Found' AS blah
)
SELECT top 1 *
FROM results
GO
[/code
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 8, 2009 at 6:22 pm
IF EXISTS(Select 1 from t_Names where id = 1234)
BEGIN
Select isNull(fName, 'N/A') from t_Names where id = 1234
END
ELSE
BEGIN
Select 'N/A'
END
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2009 at 7:26 pm
That's what we're looking for, only one of the two queries runs based on EXISTS test.
Thanks, Jeff.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 9, 2009 at 5:16 am
Hi,
Try this codes
declare @sample table ( id int, name varchar(30))
insert into @sample
select 1,null union all
select 2,'123' union all
select 3,'234' union all
select 4,'345' union all
select 5,'456' union all
select 6,null
Select isNull(name, 'N/A') from @sample a where a.id =7 and exists (select 1 from @sample where id = a.id)
if @@rowcount = 0
begin
select 'NORECORD'
end
ARUN SAS
March 9, 2009 at 7:50 am
Thanks, Arun.
Almost missed the EXISTS in the first line. I'm going to test both and see if there is any significant difference.
Jeff's version appears to be slightly more CPU efficient, but that's probably solely due to the test against @@rowcount. (I don't have time to study on the execution plans right now, because I have to attend a remedial SQL class.) The following tests were run on my laptop sending output to disk. Elapsed times varied, with Arun's code winning sometimes and Jeff's code winning sometimes. Hard to go wrong with either.
set statistics time on;
print '---------------------- Jeff'
if exists(select 1 from dbo.tally where N between 100000 and 200000)
begin
select N from dbo.tally where N between 100000 and 200000
end
else
begin
select 'nothing found'
end
print '---------------------- Arun'
select N from dbo.tally
where N between 100000 and 200000
and exists (select 1 from dbo.tally where N between 100000 and 200000)
if @@rowcount = 0
begin
select 'nothing found'
end
set statistics time off;
---------------------- Jeff
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 16 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(100001 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 439 ms.
---------------------- Arun
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(100001 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 124 ms, elapsed time = 336 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 9, 2009 at 12:25 pm
Jeff,
Thanks for the response! I have used 'If exists(blah)' in the past, it slipped my mind. Is there a way to mark the post as 'complete' or 'satisfied'? I have seen it in other forums.
Thanks,
John
March 9, 2009 at 6:24 pm
john.clyma (3/9/2009)
Jeff,Thanks for the response! I have used 'If exists(blah)' in the past, it slipped my mind. Is there a way to mark the post as 'complete' or 'satisfied'? I have seen it in other forums.
Thanks,
John
Sorry... this forum doesn't have either of those and it would be a bad thing if we did... someone else might come along with some code that no one ever thought of before. There is a way to rate the whole thread, if you want... look for "Rate Topic" in the dark blue area just above your original post on this thread.
Personally, your response is worth more than a mark. Thanks for the feedback, John.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2009 at 5:15 pm
What about...
SELECT isNull((SELECT MIN(CASE WHEN fName IS NOT NULL THEN fName ELSE 'N/A' END) FROM t_Names WHERE id = 1234),'N/A')
regards
JA
March 10, 2009 at 5:29 pm
I notice the MIN function, JA. Can you make it work with multiple rows?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 10, 2009 at 6:39 pm
:blush: No. only if i ditch that idea and left join against it from a table that has that record..
eg
select a.id, isnull(b.fName, 'N/A') fName
from (select '1234 ' id, 'test' whatever) a
left join t_Names b
on a.id = b.id where a.id = 1234
shoulda had had a closer look..
newbies!
JA
PS
is it at all possible to modify that logic for multiple records to handle an "empty return" ?
March 10, 2009 at 6:59 pm
PS
is it at all possible to modify that logic for multiple records to handle an "empty return" ?
That was the original question and that's what the examples from Jeff and Asun accomplished.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply