Passing a Var to SP that needs B-up and

Question

Post reply

Passing a Var to SP that needs B-up and

Trav williams

Grasshopper

Points: 14
More actions
October 19, 2011 at 1:48 pm

#244748

I am passing a string var to a SP. that string Var has a value of 4,11,12
I am trying to get all the varibles in table table_Type_Codes (where code = 4 or 11 or 12)
All the statements below work, except the last one. can anyone suggest how to 'Get er Done?'
Declare @Type_Code varchar(50)
set @Type_Code = '4,11,12'
select @Type_Code as Type_Code
set @Type_Code = replace(@Type_Code, ',', ''',''')
set @Type_Code = '''' + @Type_Code +''''
Select @Type_Code as Type_Code
Select * from table_Type_Codes Where (table_Type_Codes in ( '4','11','12'))
Select @Type_Code as Type_Code
Select * from table_Type_Codes Where (table_Type_Codes in (select @Type_Code ))

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You need a splitter.

Here is how the code for you case might look.

Select * from table_Type_Codes t

join DelimitedSplit8K([column_name], ',') s on s.Item = t.[column_name]

For an explanation of the logic take a look at the link in my signature about needing a splitter. Feel free to post back if you need some direction once you had a chance to read that article.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

toddasd SSCertifiable Points: 6298 More actions · Answer 2

Here is a second option. Will not be as performant as the splitter option.

Declare @Type_Code varchar(50)

set @Type_Code = '4,11,12'

Select * from table_Type_Codes Where @Type_Code like '%' + table_Type_Codes + '%'

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Todd's example will also work. If you take that approach make sure you read up on sql injection because that would be vulnerable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Actually I just noticed that is fairly safe from sql injection but it does not get you the values you want.

Declare @Type_Code varchar(50)
set @Type_Code = '4,11,12'
Select * from table_Type_Codes Where @Type_Code like '%' + table_Type_Codes + '%'

With this you will also get table_Type_Codes of 1, 2, 14, 894312. Basically it will return all records where any part of the table_Type_Codes is a numeric pattern that matches.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

drew.allen SSC Guru Points: 76997 More actions · Answer 5

toddasd (10/19/2011)
Here is a second option. Will not be as performant as the splitter option.
Declare @Type_Code varchar(50)
set @Type_Code = '4,11,12'
Select * from table_Type_Codes Where @Type_Code like '%' + table_Type_Codes + '%'

You can't be sure that this will work. It requires table_Type_Codes to be (n)varchar, but everything that the original poster said is consistent with that field being integer, decimal, et al.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

toddasd SSCertifiable Points: 6298 More actions · Answer 6

The OP didn't give the data type of table_Type_Codes, so I worked my example as it being a varchar. But it's a simple adjustment if the column is an int.

create table table_Type_Codes (table_Type_Codes int);

insert into table_Type_Codes values (4),(12),(13),(11),(20),(14),(894312);

declare @Type_Code varchar(50);

set @Type_Code = '4,11,12'

select * from table_Type_Codes where @Type_Code like '%' + cast(table_Type_Codes as varchar(10)) + '%'

--drop table table_Type_Codes;

I'm also trying to find the trouble spot you've mentioned, Sean, but I don't see the problem. Can you give an example?

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Sure.

declare @Codes varchar(25) = '14, 8, 21458'

;with cte(n) as (select 1

union all select 14

union all select 214

union all select 314

union all select 15

union all select 8)

select *

from cte

where @Codes like '%' + cast(n as varchar(10)) + '%'

From the sample data we should only get records 14 and 8. check out what it actually returns. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Trav williams Grasshopper Points: 14 More actions · Answer 8

No Luck with using the like statements. returned more results then wanted.

The Splitter, is there a function for SQL 2000? my Tsql does not like that function.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

I assumed you were on 2008 since you posted in the 2008 forum. 😉 Search the forums on here. There have been a number of string split functions posted. Some of them are on this site and others are links to elsewhere. I don't have any of those threads saved and don't have a legacy splitter available. If you have a problem finding one let me know and I will see if I can dig one up.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

toddasd SSCertifiable Points: 6298 More actions · Answer 10

I knew I had this working properly at one time...my memory isn't what I remember it used to be. :w00t:

Include the commas in the string, like so:

declare @Codes varchar(25) = '14,8,21458'

;with cte(n) as (select 1

union all select 14

union all select 214

union all select 314

union all select 15

union all select 8)

select *

from cte

where ',' + @Codes + ',' like '%,' + cast(n as varchar(10)) + ',%'

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

drew.allen SSC Guru Points: 76997 More actions · Answer 11

Sean Lange (10/19/2011)
Sure.
declare @Codes varchar(25) = '14, 8, 21458'
;with cte(n) as (select 1
union all select 14
union all select 214
union all select 314
union all select 15
union all select 8)
select *
from cte
where @Codes like '%' + cast(n as varchar(10)) + '%'
From the sample data we should only get records 14 and 8. check out what it actually returns. 😉

You can modify the LIKE statement to take care of that. The string splitter will still be faster.

select *

from cte

where ',' + @Codes ',' like '%[, ]' + cast(n as varchar(10)) + '[, ]%'

I'm using [, ] to allow for some variations in the input parameter.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA