passing a qry in as a parameter to proc?

possible to pass in a query as a parameter to a stored proc?

Number of constraints right now would make it a lot easier if I could pass in a query that selects all the ID's, tried but couldn't come up w/anything, just have a simple proc that does the deletes on 1 ID @ a time...since there are up to 100 that will need to be deleted, the qry as a param would be much more convenient. below is the proc...Thx for any help.

There are a couple of ways around this problem. It appears that you want to send a list of numbers (ids) to the stored procedure, which is undefined (e.g. it could be one id or one thousand ids).

I have seen this problem solved by passing a string of all the ids, seperated by a delimeter.

So to pass the ids 1,2,3,4 and 5 i would make my string look like this

'|1|2|3|4|5|'

| is the character we can identify as the delimiter

So in my stored procedure i could do the following (where @EmpIDs is the above string)

DELETE FROM Employees
WHERE (PATINDEX('%|'+CONVERT(VarChar,EmpID)+'|%',@EmpIDs) > 0)



Another way to do it would be to use dynamic sql. You can pass a query as a string
e.g. 'SELECT EmpID FROM Employees'

Then use Exec to execute the statement

e.g. EXEC(@Statement)

Your example would be more like:

DECLARE @Test VarChar(100)
SELECT @Test = 'SELECT TOP 10 EmpID FROM Employees (NOLOCK)'
DECLARE @Test1 VarChar(100)
SELECT @Test1 = ' DELETE FROM Employees
WHERE EmpID IN ('+@Test+')'
EXEC (@Test1)

This would delete the top 10 employees.

Hope this gives you some ideas!

ya it does...I got this solved yesterday though. I just would up passing in a delimited string of id's (used a comma) and it worked out fine. Thx for the reply!