"Jimbo" <jeremycoulter@paradise.net.nz> wrote in message
news:bzEZb.25937$ws.3118941@news02.tsnz.net...
> Hi all.
> New to this New Group, so forgive me if I ask the wrong wuestion inthe
wrong
> place.
>
> I am porting an Access DB over to MSDE.
> I build an SQL Statement in code then pass that to the Database.
> As part of the process, it builds an "IN" clause based on some check boxes
> the user selects, and this is then added the SQL variable which gets
passed
> to the DB.
>
> The problem I have is, I cant just pass the "IN" clause i.e.( 1,2,3,4) as
> part of the StoredProc variables, because I cant go :-
>
> WHERE myfield='a'
> AND myotherfield in (@INVar)
>
> I then thought maybe I need to pass each variable (of which there can be a
> max of 10) on its own, i.e.
>
> @myvar1 int = null
> @myvar2 int = null
> @myvar3 int = null etc.etc.
>
> Then use a CASE statement, i.e.
> WHERE myfield='a'
> AND myotherfield in (
> CASE WHEN @MyVar1 IS NOT NULL
then
> 1,
> CASE WHEN @MyVar2 IS NOT NULL
then
> 2,
> )
>
> But this didnt seem to work either.
>
> Is there a nice or even un-nice way I can do this other than a Dynamic
SQL,
> because I had a problem with that too...hehe ;-)
>
> Thanks, Jeremy
>
>

"Vishal Parkar" <REMOVE_THIS_vgparkar@yahoo.co.in> wrote in message
news:OzgpIbF%23DHA.2180@TK2MSFTNGP09.phx.gbl...
> hi jimbo,
>
> no need to use CASE simply put variable names in IN clause.See following
example.
> Ex:
> use northwind
> go
>
> declare @x nchar(5)
> declare @x1 nchar(5)
> select @x='alfki',@x1=null
> select * from customers
> where customerid in(@x,@x1)
>
> --
> Vishal Parkar
> vgparkar@yahoo.co.in
>
>
>