CODEWITHSUNDEEP
sql-serverstored-procedures
Jonathan Bechtel
Jonathan Bechtel

Reputation: 3617

Using rows from a table to specify inputs to stored procedures

I have a fairly complicated stored procedure which specifies its different parameters in a manner similar to this:

CREATE PROCEDURE dbo.MyStoredProcedure (
    @VAR1 AS nvarchar(50) = null,
    @VAR2 AS nvarchar(50) = null,
    @VAR3 AS nvarchar(50) = null,
    @VAR4 AS nvarchar(50) = null,
    @VAR5 AS nvarchar(50) = '1.0'
)

And so on.

The issue I'm running into is my company has a separate table they'd like to use to specify the different parameter values for executing this procedure, and have a script that runs each night to perform the procedure with the specified parameter values from each row.

Here's a simple example:

CREATE TABLE SampleTable (
    VAR1 nvarchar(MAX),
    VAR2 nvarchar(MAX),
    VAR3 nvarchar(MAX),
    VAR4 nvarchar(MAX),
    VAR5 nvarchar(MAX)
)

INSERT INTO SampleTable(COL1, COL2, COL3, COL4, COL5) VALUES 
('A', NULL, 'C', 'D', '1.0'),
('B', 'C', NULL, 'D', '1.0'),
('C', 'D', 'E', 'F', '1.0')

And the idea is that each row in SampleTable could then be run as

EXECUTE dbo.MyStoredProcedure @VAR1 = 'A' @VAR3 = 'C', @VAR4 = 'D', @VAR5 = '1.0'
EXECUTE dbo.MyStoredProcedure @VAR1 = 'B', @VAR2 = 'C', @VAR4 = 'D', @VAR5 = '1.0'
EXECUTE dbo.MyStoredProcedure @VAR1 = 'C', @VAR2 = 'D', @VAR3 = 'E', @VAR4 = 'F', @VAR5 = '1.0'

where each non-null value is then identified as the appropriate input into the stored procedure.

The idea is that someone else could update the SampleTable to reflect the values they would want to use in the stored procedure, and have a script to execute the stored procedure with the values mentioned in each row.

  1. Is this possible?
  2. Is this approach appropriate?

The only question on SO that I found related to this issue is here: pass input parameters to stored procedure from sql table, but I don't believe it adequately captures what I'm trying to do.

Am happy to take suggestions on better ways forward if there's something significantly better than what we're doing.

The idea of dynamically executing the stored procedure with the values specified in each row every night is important.

Upvotes: 0

Views: 929

Answers (1)

Dale K
Dale K

Reputation: 27471

Pop the run details into a temp table, then iterate through it as follows:

declare @Id int, @VAR1 nvarchar(50), @VAR2 nvarchar(50), @VAR3 nvarchar(50), @VAR4 nvarchar(50), @VAR5 nvarchar(50);

select VAR1, VAR2, VAR3, VAR4, VAR5, convert(bit,0) Processed, identity(int,1,1) Id /* If table doesn't already have an id column */
into #temp1
from dbo.SampleTable;

while exists (select 1 from #temp1 where Processed = 0) begin
  select top 1 @Id = Id, @VAR1 = VAR1, @VAR2 = VAR2, @VAR3 = VAR3, @VAR4 = VAR4, @VAR5 = VAR5
  from #temp1
  where Processed = 0;

  exec dbo.MyStoredProcedure @VAR1 = @VAR1, @VAR2 = @VAR2, @VAR3= @VAR3, @VAR4 = @VAR4, @VAR5 = @VAR5;

  delete
  from #temp1
  where Id = @Id;
end;

drop #temp1;

Note 1: You can control the order they are called with a simple ordering column.

Note 2: You can even put the proc name in the table if you want to split the logic out into multiple procs - so long as they keep the same parameters.

Upvotes: 2

Related Questions