create variable for IN statement
-
hi
i have a query which i need to group some codes into one code.
so...
casewhen 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
-
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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 ONGO
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
-
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
-
hi Spin,
I would look at Sommarskogs homepage for a detailed description of the problem.
Best regards,
Henrik
-
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
casewhen ProdGrpCode in ('500','510','5201','580','630','460','470','480','490') then '82'
....
is replaced simply by ResultCode.
Francesc
-
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-2 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:
[font="Courier New"]ProdGrpCodeCode
46082
47082
48082
49082
50082
51082
5201 82
58082
63082
77799
88899
99999[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply