How to handle empty return

  • 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?

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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

  • 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

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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

  • :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" ?

  • 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