Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2007
    Posts
    33

    Question Unanswered: If Nothing Selected Into Variable What Is The Value Of Variable?

    DECLARE @PayTypeValue numeric (18, 5)

    SET @PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @CompDiv AND [Deleted] = 0 AND [LoadPayType] = @LoadPay)

    IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @PayTypeValue to?

    NULL OR 0

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by tdecker81
    DECLARE @PayTypeValue numeric (18, 5)

    SET @PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @CompDiv AND [Deleted] = 0 AND [LoadPayType] = @LoadPay)

    IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @PayTypeValue to?

    NULL OR 0
    why don't you try it and see for yourself:

    SELECT @PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
    select @PayTypeValue

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Also, try
    SET @PayTypeValue = 42
    SELECT @PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
    select @PayTypeValue

    and see what happens.

  4. #4
    Join Date
    Oct 2006
    Posts
    115
    i thought it would return NULL......

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    But it returns...

    The answer would be useful for people with similar questions
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    The answer is 42.
    If the query returns no rows, the variable keeps its old value.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh that's clever - kinda like having an "else" value.
    Cheers ivon
    George
    Home | Blog

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    hmmm...

    That could be dangerous if you were using that variable in a loop.
    I would wrap the SQL in ISNULL()

    set @somevariable=ISNULL((select somevalue from sometable),-9999999)
    If @somevariable=-9999999
    'no rows were returned
    Else
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by RedNeckGeek
    That could be dangerous if you were using that variable in a loop.
    How so? It retained it's initially declared value...
    If you looped round, @PayTypeValue would equal 42 each time..?
    George
    Home | Blog

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Yes, but if you were expecting the value to change with each iteration...
    What is returned from the select statement is also dependant on
    @compdiv and @loadpay, so if those values change, it's quite possilbe that @paytypevalue would change, too.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by RedNeckGeek
    Yes, but if you were expecting the value to change with each iteration...
    That's how I found out; I got some very peculiar results.

    BTW using IsNull() won't help: the query returns no rows, so there is no null value to replace with something else.

    Solutions I'm using are
    - Setting the variable to a default value before filling it with the query,
    - Checking @@ROWCOUNT to see if any rows were returned from the query.

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think it all depends on how you put it together.

    declare @tmp integer
    set @tmp=42
    set @tmp=isnull((select 18 where 1=0),0)
    select @tmp

    Returns 0, which is what I'd expect, since
    SELECT 18 WHERE 1=0
    returns no rows
    Inspiration Through Fermentation

  13. #13
    Join Date
    Feb 2007
    Posts
    33

    @@rowcount?

    How do I use @@ROWCOUNT to do this?

    I haven't set this variable to anything before this and at least this instance doesn't loop. This SP is called once per order and checks 14 different things. Then feeds the results into a verification table. Those results and then selected by Crystal Reports and displayed to the user for an entire batch of orders one at a time by order number.

  14. #14
    Join Date
    Nov 2002
    Posts
    272
    RedNeckGeek: I now see that you put the isnull around the entire query.

    tdecker81:
    Something like
    Code:
    SELECT @l_var = value
    FROM table
    WHERE <conditions>
    
    SELECT @l_records = @@ROWCOUNT 
    
    IF @l_records > 0
       BEGIN
          ' Do stuff with @l_var
       END

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Interesting. Since I rarely use the first syntax, I was unaware of this behavior:
    Code:
    set nocount on
    
    declare	@MyValue int
    set	@MyValue = 42
    
    --This method retains @MyValue
    select	@MyValue = id from sysobjects where 1 = 0
    select	@MyValue as 'Unchanged!'
    
    --This method sets @MyValue to NULL
    set	@MyValue = (select id from sysobjects where 1 = 0)
    select	@MyValue as 'Set to NULL!'
    Learn something new every day...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •