January 9, 2011 at 7:59 pm
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
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
January 9, 2011 at 9:59 pm
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
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
Change is inevitable... Change for the better is not.
January 10, 2011 at 9:43 am
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
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
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
Change is inevitable... Change for the better is not.
January 10, 2011 at 5:14 pm
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
Change is inevitable... Change for the better is not.
January 10, 2011 at 5:15 pm
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. 🙂
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
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
January 10, 2011 at 7:03 pm
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
Change is inevitable... Change for the better is not.
January 10, 2011 at 7:22 pm
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
January 11, 2011 at 7:13 am
Agreed... either one would do a nice job on such an article.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2011 at 10:02 am
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
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