parameter question

Is it possible to use a parameter in conjuntion with an IN statement?

For example, if I have the following SQL,
select * from table where key in ('1','2','3')

How can I make the values in the IN be a param? If I do the following.
select * from table where key in (@invals)

I can't make a parameter like so,
param = new sqlparameter("@invals","'1','2','3'")
It simply returns no results.

Is there a way to make a parameter that will work?

Thanks

Marina
Tue Jan 03 14:24:10 CST 2006

You can't, there is no way to do this as far as i know. You have to
concatenate the strings together for the query.

"Jon" <ruffles_@msn.com> wrote in message
news:%23MmjmMKEGHA.3892@TK2MSFTNGP10.phx.gbl...
> Is it possible to use a parameter in conjuntion with an IN statement?
>
> For example, if I have the following SQL,
> select * from table where key in ('1','2','3')
>
> How can I make the values in the IN be a param? If I do the following.
> select * from table where key in (@invals)
>
> I can't make a parameter like so,
> param = new sqlparameter("@invals","'1','2','3'")
> It simply returns no results.
>
> Is there a way to make a parameter that will work?
>
> Thanks
>

Jon
Tue Jan 03 14:50:47 CST 2006

Darn, that's what I figured. Thanks


"Marina" <someone@nospam.com> wrote in message
news:OUEooOKEGHA.3404@TK2MSFTNGP09.phx.gbl...
> You can't, there is no way to do this as far as i know. You have to
> concatenate the strings together for the query.
>
> "Jon" <ruffles_@msn.com> wrote in message
> news:%23MmjmMKEGHA.3892@TK2MSFTNGP10.phx.gbl...
>> Is it possible to use a parameter in conjuntion with an IN statement?
>>
>> For example, if I have the following SQL,
>> select * from table where key in ('1','2','3')
>>
>> How can I make the values in the IN be a param? If I do the following.
>> select * from table where key in (@invals)
>>
>> I can't make a parameter like so,
>> param = new sqlparameter("@invals","'1','2','3'")
>> It simply returns no results.
>>
>> Is there a way to make a parameter that will work?
>>
>> Thanks
>>
>
>

W
Tue Jan 03 15:13:10 CST 2006

http://support.microsoft.com/default.aspx?scid=kb;en-us;555167
"Jon" <ruffles_@msn.com> wrote in message
news:%23MmjmMKEGHA.3892@TK2MSFTNGP10.phx.gbl...
> Is it possible to use a parameter in conjuntion with an IN statement?
>
> For example, if I have the following SQL,
> select * from table where key in ('1','2','3')
>
> How can I make the values in the IN be a param? If I do the following.
> select * from table where key in (@invals)
>
> I can't make a parameter like so,
> param = new sqlparameter("@invals","'1','2','3'")
> It simply returns no results.
>
> Is there a way to make a parameter that will work?
>
> Thanks
>

William
Tue Jan 03 18:40:17 CST 2006

Yes, this is an interesting approach and I have another I talk about in my
book that uses a Table-type Function to parse the delimited string...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"W.G. Ryan - MVP" <WilliamRyan@nospam.gmail.com> wrote in message
news:Oc9zAqKEGHA.2036@TK2MSFTNGP14.phx.gbl...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;555167
> "Jon" <ruffles_@msn.com> wrote in message
> news:%23MmjmMKEGHA.3892@TK2MSFTNGP10.phx.gbl...
>> Is it possible to use a parameter in conjuntion with an IN statement?
>>
>> For example, if I have the following SQL,
>> select * from table where key in ('1','2','3')
>>
>> How can I make the values in the IN be a param? If I do the following.
>> select * from table where key in (@invals)
>>
>> I can't make a parameter like so,
>> param = new sqlparameter("@invals","'1','2','3'")
>> It simply returns no results.
>>
>> Is there a way to make a parameter that will work?
>>
>> Thanks
>>
>
>

Jon
Wed Jan 04 10:12:02 CST 2006

Thanks. This would work well in stored procedures, but unfortunately I am
using regular sql sentences.


"W.G. Ryan - MVP" <WilliamRyan@nospam.gmail.com> wrote in message
news:Oc9zAqKEGHA.2036@TK2MSFTNGP14.phx.gbl...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;555167
> "Jon" <ruffles_@msn.com> wrote in message
> news:%23MmjmMKEGHA.3892@TK2MSFTNGP10.phx.gbl...
>> Is it possible to use a parameter in conjuntion with an IN statement?
>>
>> For example, if I have the following SQL,
>> select * from table where key in ('1','2','3')
>>
>> How can I make the values in the IN be a param? If I do the following.
>> select * from table where key in (@invals)
>>
>> I can't make a parameter like so,
>> param = new sqlparameter("@invals","'1','2','3'")
>> It simply returns no results.
>>
>> Is there a way to make a parameter that will work?
>>
>> Thanks
>>
>
>

Fox
Sat Jan 14 06:19:08 CST 2006

Hi

the solution is next ->

DECLARE @smallSQL
SET @smallSQL = "SELECT * FROM Main WHERE Price IN "+@Params
Exec (@smallSQL)
========
Where @Params can be input value of proc like (1,2,3)

:-)

good luck

"Jon" <ruffles_@msn.com> wrote in message
news:%23MmjmMKEGHA.3892@TK2MSFTNGP10.phx.gbl...
> Is it possible to use a parameter in conjuntion with an IN statement?
>
> For example, if I have the following SQL,
> select * from table where key in ('1','2','3')
>
> How can I make the values in the IN be a param? If I do the following.
> select * from table where key in (@invals)
>
> I can't make a parameter like so,
> param = new sqlparameter("@invals","'1','2','3'")
> It simply returns no results.
>
> Is there a way to make a parameter that will work?
>
> Thanks
>

W
Sat Jan 14 21:22:19 CST 2006

Another possible approach is to do the following:
http://support.microsoft.com/default.aspx?scid=kb;en-us;555167 . The guy
that wrote the article is a real genius so I figured I'd take this
opportunity to plug his article ;-)
"Fox" <mail@job.am> wrote in message
news:ORSI9SQGGHA.3984@TK2MSFTNGP14.phx.gbl...
> Hi
>
> the solution is next ->
>
> DECLARE @smallSQL
> SET @smallSQL = "SELECT * FROM Main WHERE Price IN "+@Params
> Exec (@smallSQL)
> ========
> Where @Params can be input value of proc like (1,2,3)
>
> :-)
>
> good luck
>
> "Jon" <ruffles_@msn.com> wrote in message
> news:%23MmjmMKEGHA.3892@TK2MSFTNGP10.phx.gbl...
>> Is it possible to use a parameter in conjuntion with an IN statement?
>>
>> For example, if I have the following SQL,
>> select * from table where key in ('1','2','3')
>>
>> How can I make the values in the IN be a param? If I do the following.
>> select * from table where key in (@invals)
>>
>> I can't make a parameter like so,
>> param = new sqlparameter("@invals","'1','2','3'")
>> It simply returns no results.
>>
>> Is there a way to make a parameter that will work?
>>
>> Thanks
>>
>
>