|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
parameter questionIs 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 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. Show quote "Jon" <ruffl***@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 > Darn, that's what I figured. Thanks
Show quote "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" <ruffl***@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 >> > >
http://support.microsoft.com/default.aspx?scid=kb;en-us;555167
Show quote "Jon" <ruffl***@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 > 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... -- Show quote____________________________________ 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" <ruffl***@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 >> > > Thanks. This would work well in stored procedures, but unfortunately I am
using regular sql sentences. Show quote "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" <ruffl***@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 >> > > 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 luckShow quote "Jon" <ruffl***@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 > 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 ;-) Show quote "Fox" <m***@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" <ruffl***@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 >> > >  |
|||||||||||||||||||||||
Other interesting topics