Parameter Array with dynamic SQL

I'm building an interface that will allow the user to dynamically make from
1-5 selections from a control. The @.catList parameter is a comma-delimited
string of paramater values.
When I run it and make one selection from the control, it returns a row.
However, when I make more than one selection, no rows are returned.
Should I be using "sp_executesql" or the "exec" statements in the query?
ALTER PROCEDURE sp_GetJobsByFilter
@.catList nvarchar(100),
@.Travel varchar(50),
@.Tax varchar(50)
AS
SELECT J.Category, J.Travel, J.Tax
FROM JB_Post J
INNER JOIN JB_Cats C on J.Category = C.Category WHERE C.Category IN
(@.catList) AND J.Travel = @.Travel AND J.Tax = @.Tax
RETURN
--
seandevHere are links to a couple of articles by SQL Server MVP Erland Sommarskog
you might find interesting:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"sdev" <sdev@.discussions.microsoft.com> wrote in message
news:43B76434-6EC9-41D3-90D9-1ABE20A33D92@.microsoft.com...
> I'm building an interface that will allow the user to dynamically make
> from
> 1-5 selections from a control. The @.catList parameter is a
> comma-delimited
> string of paramater values.
> When I run it and make one selection from the control, it returns a row.
> However, when I make more than one selection, no rows are returned.
> Should I be using "sp_executesql" or the "exec" statements in the query?
>
> ALTER PROCEDURE sp_GetJobsByFilter
> @.catList nvarchar(100),
> @.Travel varchar(50),
> @.Tax varchar(50)
> AS
> SELECT J.Category, J.Travel, J.Tax
> FROM JB_Post J
> INNER JOIN JB_Cats C on J.Category = C.Category WHERE C.Category IN
> (@.catList) AND J.Travel = @.Travel AND J.Tax = @.Tax
>
> RETURN
> --
> seandev