SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create variable for IN statement


create variable for IN statement

Author
Message
spin
spin
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 537
hi

i have a query which i need to group some codes into one code.

so...

case
when ProdGrpCode in ('500','510','5201','580','630','460','470','480','490') then '82'
....



is there a way i can add them to a variable and use that instead because i need to repeat the lines elsewhere.

i tried
declare @myVar as varchar(50)
set @myVar = '500, 510, 5201, 580, 630, 460, 470, 480, 490'

case
when ProdGrpCode in (@myVar) then '82'



but it doesn't work. It searches to match the entire string not each individual code. I don't really want to have to create a variable for each code.

Thanks
Steve Hall
Steve Hall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2824 Visits: 11564
Dynamic SQl would enable you to do this.
Have a look at a previous discussion for a similair example: http://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspx.

Steve Hall
Linkedin
Blog Site
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87583 Visits: 45272
Or you could use a string splitter function and then join to the results of that. Less risky than dynamic SQL (not vulnerable to SQL injection)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


tyson.price
tyson.price
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 642
Along the lines of what GilaMonster said I pass strings that I need to use in "Where in" quite a bit. This is what I use:

I have this funciton that I got off the web somewhere:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[DelimitedStringToTable](@String varchar(8000), @Delimiter char(1))

returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

GO



I use it like this:

declare @myVar varchar(50)
set @myVar = '500, 510, 5201, 580, 630, 460, 470, 480, 490'

when ProdGrpCode in (Select * from dbo.DelimitedStringToTable(@myVar,',')

The first parameter is the string to parse and the second in the character that is the delimiter. It’s the equivilant of selecting where in a sub select of a table.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87583 Visits: 45272
I wouldn't use that function. The loops will make it very slow.

Try this one: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4996 Visits: 5478
GilaMonster (10/1/2012)
I wouldn't use that function. The loops will make it very slow.

Try this one: http://www.sqlservercentral.com/articles/Tally+Table/72993/


I hate loops as well, but it will be ok if used for splitting a single list of values. Actually, properly written loop splitter will outperform Jeff Moden tally-table based split for a single list, however, if you need to use the split function for a set of rows, you better use mentioned Jeff's one.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3113 Visits: 24089
This may also work for you


case
when ', ' + @myVar + ', ' like '%, ' + ProdGrpCode + ', %' then '82'



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2042 Visits: 1222
hi Spin,

I would look at Sommarskogs homepage for a detailed description of the problem.

Best regards,
Henrik



gofrancesc
gofrancesc
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 812
Hello,
my first thought is create a temp table or var table including these relations, then join.
CREATE TABLE #T (ProdGrpCode varchar(10), ResultCode VARCHAR(10))
INSERT INTO #T
SELECT '500', '82' UNION SELECT '510', '82' UNION SELECT '5201', '82' UNION
SELECT '580', '82' UNION SELECT '630', '82' UNION SELECT '460', '82' UNION
SELECT '470', '82' UNION SELECT '480', '82' UNION SELECT '490', '82'


Now, your reference
case
when ProdGrpCode in ('500','510','5201','580','630','460','470','480','490') then '82'
....


is replaced simply by ResultCode.

Francesc
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 1721
Another option using a table valued function. In this example I use an outer join but if you only want the code '82' values an inner join would work as well.



/* Table valued function for parsing a delimited array into a table */

CREATE FUNCTION [dbo].[tvfParseDelimitedString]
(
@S NVARCHAR(MAX) -- Delimited input string
,@Split CHAR(1) -- Delimiter used for the input string
)
RETURNS @Table TABLE
(
[ID] INT NOT NULL IDENTITY(1,1)
,[Value] NVARCHAR(MAX) NULL
,PRIMARY KEY ([ID])
,UNIQUE ([ID])
)
BEGIN

DECLARE @X XML

SET @X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>')

INSERT INTO @Table
SELECT LTRIM(T.c.value('.','NVARCHAR(MAX)')) AS [Value]
FROM @X.nodes('/root/s') T (c)

RETURN

END


/* Create a set of test data */

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (ProdGrpCode INT)
INSERT INTO #TempTable
SELECT '500' UNION SELECT '510' UNION SELECT '5201' UNION
SELECT '580' UNION SELECT '630' UNION SELECT '460' UNION
SELECT '470' UNION SELECT '480' UNION SELECT '490' UNION
SELECT '777' UNION SELECT '888' UNION SELECT '999'


/* The final query that takes the delimited input */
/* and assigns the proper code */

DECLARE @myVar AS NVARCHAR(50)
SET @myVar = N'500, 510, 5201, 580, 630, 460, 470, 480, 490'

SELECT
ProdGrpCode
,(CASE
WHEN ProdGrpCode = Value THEN 82
ELSE 99
END) AS Code
FROM
#TempTable AS t
LEFT OUTER JOIN
(SELECT Value FROM dbo.tvfParseDelimitedString(@myVar,',')) AS v
ON t.ProdGrpCode = v.Value




Output:

ProdGrpCode Code
460 82
470 82
480 82
490 82
500 82
510 82
5201 82
580 82
630 82
777 99
888 99
999 99




 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search