Post reply

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

  • January 9, 2011 at 7:59 pm

    #231854

    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!

  • January 9, 2011 at 9:29 pm

    #1271238

    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

  • January 9, 2011 at 9:59 pm

    #1271239

    Very nice! Thank you!

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

  • January 9, 2011 at 11:05 pm

    #1271247

    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.

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

  • January 10, 2011 at 9:43 am

    #1271478

    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!

  • January 10, 2011 at 11:10 am

    #1271531

    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

  • January 10, 2011 at 5:09 pm

    #1271668

    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.

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

  • January 10, 2011 at 5:14 pm

    #1271669

    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.

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

  • January 10, 2011 at 5:15 pm

    #1271670

    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

  • January 10, 2011 at 6:53 pm

    #1271691

    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

  • January 10, 2011 at 7:03 pm

    #1271693

    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.

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

  • January 10, 2011 at 7:22 pm

    #1271695

    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

  • January 11, 2011 at 7:13 am

    #1271869

    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.

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

  • January 11, 2011 at 10:02 am

    #1271958

    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.

  • January 11, 2011 at 10:39 am

    #1271980

    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 15 total)

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