Assigning values to multiple variables with single SELECT (with a twist)

  • Hi folks!

    Here is what I am trying to accomplish and I was wondering if there is a clever way to do this. Setup script:

    USE tempdb

    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#t]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[#t]

    CREATE TABLE #t (id int, varvalue varchar(32))

    INSERT INTO #t (id, varvalue)

    VALUES (1, 'var1')

    INSERT INTO #t (id, varvalue)

    VALUES (2, 'var2')

    INSERT INTO #t (id, varvalue)

    VALUES (3, 'var3')

    DECLARE @var1 varchar(32),

    @var2 varchar(32),

    @var3 varchar(32)

    SELECT @var1 = varvalue

    FROM #t

    WHERE id = 1

    SELECT @var2 = varvalue

    FROM #t

    WHERE id = 2

    SELECT @var3 = varvalue

    FROM #t

    WHERE id = 3

    SELECT @var1, @var2, @var3

    As you can see I am using three SELECTs to get each of the variables assigned. Is there a way to do it in a single SELECT? (you can assume I am guaranteed that my table will always contain just one value for each id)

    Thank you!

  • How does this work out for you?

    SELECT @var1 = max(CASE WHEN id = 1 THEN varvalue ELSE NULL END),

    @var2 = max(CASE WHEN id = 2 THEN varvalue ELSE NULL END),

    @var3 = max(CASE WHEN id = 3 THEN varvalue ELSE NULL END)

    FROM #t

    WHERE ID BETWEEN 1 AND 3;

    SELECT @var1, @var2, @var3;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Very nice! Thank you!

    Since I have a ton of these assignments in my case, I am hoping for some marginal performance improvement.

  • mishaluba (1/9/2011)


    Very nice! Thank you!

    Since I have a ton of these assignments in my case, I am hoping for some marginal performance improvement.

    Then I have to ask, why are you using variables for this which smacks a bit of RBAR? What is it that you're actually trying to do? I ask because there may be a better way and, if there is, we'd be happy to share it with you.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Without going into too much detail, I am tasked with optimizing some code written before my time. There is a stored procedure, which takes a parameter containing delimited list of values. BTW, one of the optimizations we will be implementing is using brilliant function written by Mr. Moden and others for parsing delimited list :-). Once parsed into a table, the values are assigned to internal procedure variables and used for some further fairly evolved business logic. I hope this makes sense.

    Thank you!

  • Since you're using SQL 2008, can you switch from delimited lists of parameter values to a table variable parameter? Those are more efficient.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (1/10/2011)


    Since you're using SQL 2008, can you switch from delimited lists of parameter values to a table variable parameter? Those are more efficient.

    Ah... guess I need to teach myself something new. I've not tried passing table variable parameters, yet.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • mishaluba (1/10/2011)


    Without going into too much detail, I am tasked with optimizing some code written before my time. There is a stored procedure, which takes a parameter containing delimited list of values. BTW, one of the optimizations we will be implementing is using brilliant function written by Mr. Moden and others for parsing delimited list :-). Once parsed into a table, the values are assigned to internal procedure variables and used for some further fairly evolved business logic. I hope this makes sense.

    Thank you!

    Thanks for the very nice compliment, :blush: but the original idea isn't mine. I just wrote about it because it didn't look like many people knew about Tally/Numbers tables.

    I'm still concerned about using variables to accomplish your "fairly evolved business logic". The use of such variables in other than an UPDATE clause is indicative of some fairly heavy RBAR. If you'd care to share the requirements and maybe some readily consumable test data, we may be able to help you can the RBAR in favor of some high speed, set based code.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • GSquared (1/10/2011)


    Since you're using SQL 2008, can you switch from delimited lists of parameter values to a table variable parameter? Those are more efficient.

    I haven't had the opportunity to use them at a business level yet, but they're dang handy. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (1/10/2011)


    GSquared (1/10/2011)


    Since you're using SQL 2008, can you switch from delimited lists of parameter values to a table variable parameter? Those are more efficient.

    I haven't had the opportunity to use them at a business level yet, but they're dang handy. 🙂

    Sounds like a "SQL Spackle" opportunity for someone... :w00t:

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS (1/10/2011)


    Craig Farrell (1/10/2011)


    GSquared (1/10/2011)


    Since you're using SQL 2008, can you switch from delimited lists of parameter values to a table variable parameter? Those are more efficient.

    I haven't had the opportunity to use them at a business level yet, but they're dang handy. 🙂

    Sounds like a "SQL Spackle" opportunity for someone... :w00t:

    Agreed... Gus? What say thee?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden (1/10/2011)


    WayneS (1/10/2011)


    Craig Farrell (1/10/2011)


    GSquared (1/10/2011)


    Since you're using SQL 2008, can you switch from delimited lists of parameter values to a table variable parameter? Those are more efficient.

    I haven't had the opportunity to use them at a business level yet, but they're dang handy. 🙂

    Sounds like a "SQL Spackle" opportunity for someone... :w00t:

    Agreed... Gus? What say thee?

    Actually, it was meant towards Craig... but Gus works also!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Agreed... either one would do a nice job on such an article.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Thank you for your willingness to help! Gotta love SSC! In this case there is no RBAR going on (I think). The "evolved" business logic has to do with some calls to external components performing data encryption/decryption.

    Jeff Moden (1/10/2011)


    mishaluba (1/10/2011)


    Without going into too much detail, I am tasked with optimizing some code written before my time. There is a stored procedure, which takes a parameter containing delimited list of values. BTW, one of the optimizations we will be implementing is using brilliant function written by Mr. Moden and others for parsing delimited list :-). Once parsed into a table, the values are assigned to internal procedure variables and used for some further fairly evolved business logic. I hope this makes sense.

    Thank you!

    Thanks for the very nice compliment, :blush: but the original idea isn't mine. I just wrote about it because it didn't look like many people knew about Tally/Numbers tables.

    I'm still concerned about using variables to accomplish your "fairly evolved business logic". The use of such variables in other than an UPDATE clause is indicative of some fairly heavy RBAR. If you'd care to share the requirements and maybe some readily consumable test data, we may be able to help you can the RBAR in favor of some high speed, set based code.

  • Jeff Moden (1/10/2011)


    WayneS (1/10/2011)


    Craig Farrell (1/10/2011)


    GSquared (1/10/2011)


    Since you're using SQL 2008, can you switch from delimited lists of parameter values to a table variable parameter? Those are more efficient.

    I haven't had the opportunity to use them at a business level yet, but they're dang handy. 🙂

    Sounds like a "SQL Spackle" opportunity for someone... :w00t:

    Agreed... Gus? What say thee?

    I have used them in a business environment, and they worked great. I'll see what I can write up on it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply