Stored Procedure Parameter

[John Hadzima]

Is it possible to pass a parameter to a stored procedure containing a text
string that you can use as a where clause in the stored procedure? I know
you can pass individual parameters for the where clause but the number of
items in my where clause will be variable so I would like to put them all
in a single variable.

For example, in the stored procedure the variable '@skillstr' would contain
the where clause:

Alter Procedure Skills @empid INT, @skillstr CHAR(100)
As
SELECT skill_id, skill_name FROM skills WHERE @skillstr ORDER BY
skill_name

When calling the stored procedure you would pass the where clause as a text
string which would have a different number of items each time:

EXEC BuildExp @empid=1, @skillstr="skill_id=27 OR skill_id=28"

OR

EXEC BuildExp @empid=1, @skillstr="skill_id=27 OR skill_id=28 OR skill_id=30"


The syntax on this is not correct but is this possible written another way?
Any help would be appreciated.

[Colin McGuigan]

John Hadzima wrote in message <395a0509@news.devx.com>...
>
>Is it possible to pass a parameter to a stored procedure containing a text
>string that you can use as a where clause in the stored procedure? I know
>you can pass individual parameters for the where clause but the number of
>items in my where clause will be variable so I would like to put them all
>in a single variable.
>
>For example, in the stored procedure the variable '@skillstr' would contain
>the where clause:
<snip>

Create the SQL string inside the stored procedure, and then EXEC it.

DECLARE @SQL varchar(255)

SET @SQL = 'SELECT skill_id, skill_name FROM skills WHERE ' + @skillstr + '
ORDER BY skill_name'

EXEC (@SQL)


--
Colin McGuigan

[Narayana Pakala]

I assume that you are using Microsoft SQL Server.

Try sp_executesql.

For this you got to put your sql statement in a string and pass it as a parameter
to sp_executesql.

How to: Create a nvarchar string in your stored procedure
assign your sql statement to that string
Concateate your params to your sql statement
pass this final string to sp_executesql


"John Hadzima" <jhadzima@alphas.com> wrote:
>
>Is it possible to pass a parameter to a stored procedure containing a text
>string that you can use as a where clause in the stored procedure? I know
>you can pass individual parameters for the where clause but the number of
>items in my where clause will be variable so I would like to put them all
>in a single variable.
>
>For example, in the stored procedure the variable '@skillstr' would contain
>the where clause:
>
>Alter Procedure Skills @empid INT, @skillstr CHAR(100)
>As
> SELECT skill_id, skill_name FROM skills WHERE @skillstr ORDER BY
>skill_name
>
>When calling the stored procedure you would pass the where clause as a text
>string which would have a different number of items each time:
>
>EXEC BuildExp @empid=1, @skillstr="skill_id=27 OR skill_id=28"
>
>OR
>
>EXEC BuildExp @empid=1, @skillstr="skill_id=27 OR skill_id=28 OR skill_id=30"
>
>
>The syntax on this is not correct but is this possible written another way?
>Any help would be appreciated.
>
>
>
>