Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create variable for IN statement Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 2:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:28 AM
Points: 116, Visits: 409
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


Post #1366387
Posted Monday, October 1, 2012 2:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 1,541, Visits: 8,185
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.


BrainDonor
Linkedin
Blog Site
Post #1366394
Posted Monday, October 1, 2012 2:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #1366395
Posted Monday, October 1, 2012 5:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:26 AM
Points: 74, Visits: 620
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.
Post #1366490
Posted Monday, October 1, 2012 6:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #1366517
Posted Monday, October 1, 2012 7:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,836, Visits: 5,066
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1366564
Posted Monday, October 1, 2012 7:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550

This may also work for you

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



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1366567
Posted Tuesday, October 2, 2012 2:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:07 AM
Points: 1,362, Visits: 989
hi Spin,

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

Best regards,
Henrik



Post #1366890
Posted Tuesday, October 2, 2012 3:48 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:52 AM
Points: 634, Visits: 809
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
Post #1366926
Posted Tuesday, October 2, 2012 11:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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




 
Post #1367187
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse