Problem with Dynamic Where Clause

I have a stored proc that accepts a varchar as a parameter. What is being
passed in is one or more IDs in a comma separated list. (ie '123,567,789')
In the where clause I want to pull are records where a value is IN the list
being passed in. (ie. WHERE column IN (@.Var))
When I run this stored proc I get an error because its treating
'123,567,789' as one varchar value insstead of 3 int values.
Is there anything I can do to work around this?
Thanks in advance,
Mike RFaking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Mike" wrote:

> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>|||Mike wrote:
> I have a stored proc that accepts a varchar as a parameter. What is
> being passed in is one or more IDs in a comma separated list. (ie
> '123,567,789') In the where clause I want to pull are records where a
> value is IN the list being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
You have to use dynamic SQL to do what you want and you'll have to grant
users select access on the tables in question if they don't already have
those rights. That might be asecurity risk in your environment.
declare @.n nvarchar(1000)
Set @.n = N'Select col1 from mytable where col2 in (' + @.Var + N')'
Exec sp_executesql @.n
The other option is to create a temp table with all those IDs and join
the temp table with the main table in the query.
David Gugick
Imceda Software
www.imceda.com|||In article <Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl>,
mraeNOSPAM@.NOSPAMATALLcalibrus.com says...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>
http://www.sommarskog.se/arrays-in-...ist-of-integers
****************************************
************************
Tapio Kulmala
"Those are my principles. If you don't like them I have others."
- Groucho Marx
****************************************
************************|||http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <mraeNOSPAM@.NOSPAMATALLcalibrus.com> wrote in message
news:Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the
list
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>|||If you use the attached User Defined Function to convert the Delimited list
in =to a table variable, you can simply join your main query to this table
variable...
Here's the UDF
Create Function dbo.ParseString (
@.S VarChar(8000), @.delim Char(1))
Returns @.tOut Table
(ValNum Integer Primary Key Identity,
sVal VarChar(1000))
As
Begin
Declare @.sVal VarChar(1000)
Declare @.dPos Integer
Declare @.Start Integer Set @.Start = 1
-- --
If @.S = @.delim Or Len(@.S) = 0 Return
Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
-- --
Set @.dPos = CharIndex(@.delim, @.S, 1)
While @.dPos <> 0
Begin
Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
Insert @.tOut (sVal) Values (@.sVal)
Set @.Start = @.dPos + 1
Set @.dPos = CharIndex(@.delim, @.S, @.Start)
End
Return
-- ---
End
And in your stoored Proc, just join to the output of this udf as though it
was a table, containing a varchar() named sVal...
Select <stuff>
From Table T
Join dbo.ParseString(@.Var, ',') as V
On T.ColumnName = Cast(V.sVal as Integer)
"Mike" wrote:

> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>