Execute stored procedure with multiple parameter values

Question

I have a simple stored procedure, which executes in base of one input parameter. But I have to execute it about 100 times because of different parameters (parameter is int)Is there any other way except of inputing parameter values one by one? I mean something like @parameter in (value 1, value 2, .....value n).Thanks a lot. This is the procedure:

ALTER PROCEDURE procname
@parameter AS BIGINT

AS

DELETE FROM tbl1 WHERE id = @parameter
DELETE FROM tbl2 WHERE tbl2_field=@parameter 
DELETE FROM tbl3 WHERE tbl3_field=@parameter
DELETE FROM tbl4 WHERE tbl4_field=@parameter

you can follow same way `set @parameter = value 1, value 2, .....value n;ColName in (@parameter)` — wiretext, Sep 17 '15 at 12:37

score 0 · Answer 1 · answered Sep 17 '15 at 12:33

0

1) make the parameter optional 2) when parameter is provided, use it to filter the results 3) when parameter is null, return all results, grouped on the field represented by the parameter.

Such as :

Select   EmpNum, EmpNameFull, DeptName, HireDate
From     Employee
Where    DeptName = paramDeptName
or   paramDeptName IS NULL

Order By DeptName , EmpNameFull

answered Sep 17 '15 at 12:33

Juan-Carlos

377
2
8

my procedure includes delete in several tables – Silva Çala Sep 17 '15 at 12:41
Can you attach the procedure ? – Juan-Carlos Sep 17 '15 at 12:52
Do you need the results to be in separate result sets, or can they be in one result set ? – Juan-Carlos Sep 17 '15 at 12:58
When you execute the existing proc for those ~100 different parameters, is that the full range of possible parameters, or are other potential parameter values going unused ? – Juan-Carlos Sep 17 '15 at 12:58
no, all paramters will be unused. it is just a maintenance procedure, but according to this values, some records will be deleted from different tables – Silva Çala Sep 17 '15 at 13:36
results may be in one or different result sets it doesnt matter – Silva Çala Sep 17 '15 at 13:36
Without seeing your stored procedure, I can't really speak to the matter of how to carry out the delete operations. As for returning the results, I would point back to the example in my answer and how it uses the OR condition in the WHERE clause, either applying the parameter value, or skipping the use of the parameter altogether and including results covering all possibilities. If the ~100 iterations you currently employ, each with its own parameter value, covers all the possible param values, then this seems to me like it would work. Your thoughts ? – Juan-Carlos Sep 17 '15 at 14:43
Maybe I have mis-understood. I am assuming this is about obtaining return results, but as I reread your message I notice that you say it is a maintenance procedure ( and now I see your sample code ). Based on that, my above answer wont fit your problem. – Juan-Carlos Sep 17 '15 at 14:48
Thanks anyway Juan-Carlos – Silva Çala Sep 18 '15 at 06:24

score 0 · Answer 2 · edited May 23 '17 at 12:14

You could pass an array to the stored procedure. This is already answered with different database versions and multiple solutions on Stack Overflow here:

How to pass an array into a SQL Server stored procedure

and here

Passing an array of parameters to a stored procedure

Another solution could be to send the parameter comma separated and split it in T-SQL and loop through the values, How to split a comma separated string and loop its values in SQL Server (string parameter would be needed).

Execute stored procedure with multiple parameter values

2 Answers2