Passing in a Parameter

Hi All,
I have a Stored Proc and would like to pass in a VARCHAR parameter. The
problem is that the query will need to use it in a IN clause...
DECLARE @.PARAM AS VARCHAR(20)
SET @.PARAM = 'Dan', 'Mike', 'Lisa'
SELECT *
FROM Orders
WHERE Name IN (@.PARAM)
is there a away around this?
Thanks,
Kunkel
On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:

>Hi All,
>I have a Stored Proc and would like to pass in a VARCHAR parameter. The
>problem is that the query will need to use it in a IN clause...
>DECLARE @.PARAM AS VARCHAR(20)
>SET @.PARAM = 'Dan', 'Mike', 'Lisa'
>SELECT *
>FROM Orders
>WHERE Name IN (@.PARAM)
>is there a away around this?
Hi Kunkel,
Several. And they are all described and compared at
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||thanks for that link! it was very helpful. but after i implemented the
articles ideas, i came across this solution:
DECLARE @.Var AS VARCHAR(100)
SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
DECLARE @.SQL AS varchar(1000)
SET @.SQL = 'SELECT *
FROM ORDERS
WHERE
NAME IN (' + @.Var + ')'
Exec(@.SQL)
"Hugo Kornelis" wrote:

> On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:
>
> Hi Kunkel,
> Several. And they are all described and compared at
> http://www.sommarskog.se/arrays-in-sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 28 Apr 2005 15:30:02 -0700, Kunkel wrote:

>thanks for that link! it was very helpful. but after i implemented the
>articles ideas, i came across this solution:
>DECLARE @.Var AS VARCHAR(100)
>SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
>DECLARE @.SQL AS varchar(1000)
>SET @.SQL = 'SELECT *
>FROM ORDERS
>WHERE
>NAME IN (' + @.Var + ')'
>Exec(@.SQL)
Hi Kunkel,
This is dynamic SQL, and I believe that this techinique is discussed at
Erland's site as well. Please don't do this if you can avoid it. It is a
severe breach of security - you give malicious users the ability to
inject SQL.
Erland has a great article with an in-depth explanation of pros and cons
of dynamic SQL as well: http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)