Re: (SQL) Using Variable in select command
- From: "Daniel Crichton" <msnews@xxxxxxxxxxxxxxxx>
- Date: Tue, 2 May 2006 15:53:37 +0100
Robert wrote on Tue, 2 May 2006 07:35:03 -0700:
I am trying to use the following select statement in the stored procedure
Select * from mytable where PIN in (@MyPINS)
PIN is varchar(5) and @MyPINS is varchar(50) that i get it as input to the
stored procedure.
when i give the input to @MyPINS like 1,2,3 i get no answer but if i write
the query like
Select * from mytable where PIN in (1,2,3)
i get some output. i think i know what is the problem. the problem is that
the query get the @MyPINS as '1,2,3' and the query will be like
Select * from mytable where PIN in ('1,2,3')
but i don't know how to solve the proble.
please help me.
Thanks
You can't use a variable like that. You will need to construct "dynamic
sql", eg.
/*create a variable, this can be replaced with a parameter later */
/* if PIN is varchar(5), make @MyPINS big enough to hold as many possible
PIN values, change the 10 as needed */
Declare @MyPINS varchar(10)
Set @MyPINS = '1,2,3'
/*add quotes to the variable as required */
Set @MyPINS = '''' + REPLACE(@MyPINS, ',' , ''',''') + ''''
/* execute the dynamic sql */
exec 'SELECT * FROM MyTable WHERE PIN IN (' + @MyPINS + ')'
The above might not run - I typed it from memory while taking a teabreak, so
I might have missed something.
Dan
.
- Prev by Date: Re: Slow access on XP using ADO with ODBC to SQL Server 2000 - any ideas?
- Next by Date: Re: Slow access on XP using ADO with ODBC to SQL Server 2000 - any ideas?
- Previous by thread: Re: Slow access on XP using ADO with ODBC to SQL Server 2000 - any ideas?
- Next by thread: kb838405
- Index(es):
Relevant Pages
|
|
