Feed stored procedure with SELECT resultset -- sql server (alternate)

dgasser NO[at]SPAM ch.oetiker.com
10/21/2004 7:19:02 AM

I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 has
about 50 input parameters. PROC2 is the main procedure that does some
data modifications and afterwards calls PROC1 using an EXECUTE
statement.

The input parameter values for PROC1 are stored in a table in my
database. What I like to do is passing those values to PROC1 using a
SELECT statement. Currently, all 50 parameters are read and stored in
a variable, and afterwards they are passed to PROC1 using:

EXEC spPROC1 @var1, @var2, @var3, ... , @var50

Since it is a lot of code declaring and assigning 50 variables, I was
wondering if there is a possibility to run a statement like:

EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)

Ross Presser
10/21/2004 3:30:09 PM

[quoted text, click to view]

You could build up a dynamic SQL statement and execute it that way, but I
think it would be vastly better if your spPROC1 read its inputs to be in a

sql server (alternate) : Feed stored procedure with SELECT resultset