locked
If No Rows Are Returned From Query Do Variables Always Equal NULL in SELECT RRS feed

  • Question

  • I know when you declare a variable in SQL it is NULL by default.  Do the variables in my SELECT statement always be equal to NULL if no rows are returned in the query?  Specifically my @bintFinalResultID variable.

     

    DECLARE @bitExported bit = 0,
    		@intAnalysis_PK bigint, 
    		@bintAnalyteCodeID bigint,
    		@bintExportedResultID bigint,
    		@bintBatchID bigint,
    		@bintFinalResultID bigint,
    		@Msg varchar
    
    -- determine if a result for the sample-analyte has already been exported
    SELECT @bintFinalResultID = E.FinalResultID,
    	@bintBatchID = R.BatchID,
    	@bintExportedResultID = E.ResultID
    FROM tbl_data_RES_Base AS R
    	inner join tbl_data_Expected as E
    		on R.intAnalysis_PK = E.intAnalysis_PK
    			and R.bintAnalyteCodeID = E.bintAnalyteCodeID
    WHERE R.ResultID = @ResultID
    	AND E.booExported = 1
    
    -- if no other associated sample-analyte results have been exported
    -- clear from expected table and unset ready in results table
    
    IF @bintFinalResultID IS NOT NULL
    
    	-- report back the BatchID & ResultID of the exported result
    	SET @Msg = 'Result ID ' + @bintExportedResultID + ' from Batch ID ' + @bintBatchID + ' has already been exported for this sample.'
    	RAISERROR(@Msg, 0, 1)
    
    ELSE
    

    Thanks in advance,

    Ryan

     

     

     


    Ryan
    Thursday, June 9, 2011 7:26 PM

Answers

  • The Value of variable will not change if query does not return any rows. If it is previosly NULL then it will remain NULL and if it 'False' in it will remain 'False'
    If this answer is helpful to you .. Please mark as Answer....
    • Marked as answer by Ryan0827 Friday, June 10, 2011 11:32 AM
    Thursday, June 9, 2011 7:35 PM
  • If you do a SELECT <variable name> = <expression> From <whatever> and <whatever> returns 0 rows, the the value of <variable name> will not change.  So if it was NULL before the SELECT,, it ill be NULL afterwards.  But if it was something else, it will remain the other value.  On the other hand, if you use a SET statement, then the value will be changed to NULL.  For example, run the following to see the results.

    Declare @Test int;
    Set @Test = 1;
    Select @Test = OBJECT_ID From sys.objects Where object_id < 0 And object_id > 5;
    Select @Test As ValueFromSelect;
    Set @Test = (Select OBJECT_ID From sys.objects Where object_id < 0 And object_id > 5);
    Select @Test As ValueFromSet;
    
    

    Tom

    • Proposed as answer by Naomi N Thursday, June 9, 2011 7:55 PM
    • Marked as answer by Ryan0827 Friday, June 10, 2011 11:32 AM
    Thursday, June 9, 2011 7:35 PM
  • You will see it in the Results tab.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Ryan0827 Friday, June 10, 2011 11:32 AM
    Thursday, June 9, 2011 7:56 PM

All replies

  • The Value of variable will not change if query does not return any rows. If it is previosly NULL then it will remain NULL and if it 'False' in it will remain 'False'
    If this answer is helpful to you .. Please mark as Answer....
    • Marked as answer by Ryan0827 Friday, June 10, 2011 11:32 AM
    Thursday, June 9, 2011 7:35 PM
  • It would be more appropriate to use:

    IF @@ROWCOUNT = 0 THEN

     

    • Proposed as answer by RaheelKhan Friday, June 10, 2011 10:51 AM
    Thursday, June 9, 2011 7:35 PM
  • If you do a SELECT <variable name> = <expression> From <whatever> and <whatever> returns 0 rows, the the value of <variable name> will not change.  So if it was NULL before the SELECT,, it ill be NULL afterwards.  But if it was something else, it will remain the other value.  On the other hand, if you use a SET statement, then the value will be changed to NULL.  For example, run the following to see the results.

    Declare @Test int;
    Set @Test = 1;
    Select @Test = OBJECT_ID From sys.objects Where object_id < 0 And object_id > 5;
    Select @Test As ValueFromSelect;
    Set @Test = (Select OBJECT_ID From sys.objects Where object_id < 0 And object_id > 5);
    Select @Test As ValueFromSet;
    
    

    Tom

    • Proposed as answer by Naomi N Thursday, June 9, 2011 7:55 PM
    • Marked as answer by Ryan0827 Friday, June 10, 2011 11:32 AM
    Thursday, June 9, 2011 7:35 PM
  • Tom,

    How can I see the value of @Test in Microsoft SQL Server Management Studio?  In the Messages tab it only says "Command(s) completed successfully."  I don't believe I have access to debug mode.

    Thanks,Ryan


    Ryan
    Thursday, June 9, 2011 7:52 PM
  • You will see it in the Results tab.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Ryan0827 Friday, June 10, 2011 11:32 AM
    Thursday, June 9, 2011 7:56 PM
  • Hello Ryan, 

    you can use the below skelton to check if query returned no result, as

     

    Declare @timertest varchar(10)
    Insert into #test values('1:00:00')
    
    Select @timertest = timer from #test where timer='9:00:00'
    IF @@ROWCOUNT = 0 
    	print 'File alreayd exported'
    else
    	print 'else'

     

    Thanks,


    Raheel Khan
    Friday, June 10, 2011 10:55 AM
  • Is this post related to your previous post from yesterday?  Note that I asked this question after there was confusion related to your previous question.  As has been mentioned -- if there are no results then the values of the variables will remain unchanged.

     

    Friday, June 10, 2011 11:23 AM