using the where in () statment with a variable.

Last Post 22 Jul 2006 06:11 AM by mwesch. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
werice
New Member
New Member

--
21 Jul 2006 05:27 AM
I have a query that I am passing a parameter to, see below:

Create procedure GetInfo (@TreatyNumbers)

As
print @TreatyNumbers (I do this for testing and I see the nubers)

select * from MyTable where treatynumber in (@TreatyNumbers)



I call the query as done below:

exec GetInfo @TreatyNumbers = "'000004','000102','006XNX','000578','004400'"

I get no data returned, but if I hard code the numbers in GetInf, I get data back. I know thw data is there how come in () with a parameter or variable does not pick up the information? Should I be doing this some other whay!!!

Thanks ahead of time.

Bill

nosepicker
New Member
New Member

--
21 Jul 2006 07:25 AM
Doing it that way unfortunately won't give you what you want. When you pass in the values that way, SQL Server treats it as one entire value, not separate delimited values. So it's trying to find treatynumbers that equal the entire string '000004','000102','006XNX','000578','004400'. Therefore, to do it this way, you need to use dynamic SQL. Also, you need to set @TreatyNumbers differently (use a series of single quotes - no double quotes):

DECLARE @sql varchar(1000)
SET @TreatyNumbers = '''000004'',''000102'',''006XNX'',''000578'',''004400'''

SET @sql = 'select * from MyTable where treatynumber in (' + @TreatyNumbers + ')'

EXEC @sql

Just be careful of SQL injection issues when using dynamic SQL.
mwesch
New Member
New Member

--
22 Jul 2006 06:11 AM
I often use a table-valued UDF for this sort of situation. Create a UDF that parses @TreatyNumbers and returns the values as a table, then you can do this.

select *
from MyTable
where treatynumber in (select Value from udf_SplitTreatyNumberString(@TreatyNumbers))

or

select a.*
from MyTable a
inner join udf_SplitTreatyNumberString(@TreatyNumbers) b
on a.TreatyNumber = b.Value


Here's example script to create the UDF.
-------------------------------------------------------------------
create function udf_SplitTreatyNumberString
(
@TreatyNumbers varchar(1000),
@Delimiter char(1)
)

returns @Results table
(Value varchar(10))
as
begin
declare @Position int
while len(@TreatyNumbers) > 0
begin
set @Position = charindex(@Delimiter, @TreatyNumbers)
if @Position > 0
begin
insert @Results
select left(@TreatyNumbers, @Position - 1)
set @TreatyNumbers = right(@TreatyNumbers, len(@TreatyNumbers) - @Position)
end
else
begin
insert @Results
select @TreatyNumbers
set @TreatyNumbers = ''
end
end
return
end
nosepicker
New Member
New Member

--
22 Jul 2006 12:04 PM
That's a good suggestion Michael. It might seem like more work, but it helps avoid the dangerous sql injection issue.
SQLUSA
New Member
New Member

--
29 Jul 2006 10:31 PM
Why am I not scared of this DANGEROUS sql injection issue?

Did it happen to you?

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
nosepicker
New Member
New Member

--
30 Jul 2006 08:56 PM
Why are you not scared of sql injection? I don't know. Why should I care? Why should anyone else care? Ignorance is bliss, isn't it? I hope you believe in prayer.


Acceptable Use Policy
---