Feed stored procedure with SELECT resultset

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)

Any help on this is greatly appreciated!On 21 Oct 2004 07:19:02 -0700, Dieter Gasser wrote:

> 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)
> Any help on this is greatly appreciated!

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
table, rather than as parameters.

FEATURE REQUEST: Jump to Report with rs:, rc: parameters

FEATURE REQUEST (already submitted to sqlwish@.microsoft.com): Allow rs: and
rc: parameters as an exception to the "CLR-compliant parameter name" rule
within Navigation Parameter properties.
Background: I have created a custom report manager that displays reports
within an IFRAME. With URL access, the rc:LinkTarget points to the IFRAME
name.
Problem: Using the "Jump to report" navigation does not carry the
rc:LinkTarget=iframename parameter forward to the linked report. Subsequent
clicks break the report out of the IFRAME. The workaround, dynamically
creating the URL, is cumbersome and limits report portability. In the
Navigation tab (Parameters button), I tried to set rc:LinkTarget as a
parameter with iframename as the value, which produced a compilation error,
that parameter names must be CLR-compliant. This is true except for rs: and
rc: parameters. Please consider allowing a report to compile and publish
with rs: and rc: parameters specified in the Jump to... links.
Thanks,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---I should have mentioned an alternative (also sent to sqlwish):
FEATURE REQUEST ALTERNATIVE: Provide an option to automatically carry
rc:LinkTarget paramters forward in linked (Jump to) reports.
Thanks,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:usQBm8DzEHA.2624@.TK2MSFTNGP11.phx.gbl...
> FEATURE REQUEST (already submitted to sqlwish@.microsoft.com): Allow rs:
> and rc: parameters as an exception to the "CLR-compliant parameter name"
> rule within Navigation Parameter properties.
> Background: I have created a custom report manager that displays reports
> within an IFRAME. With URL access, the rc:LinkTarget points to the IFRAME
> name.
> Problem: Using the "Jump to report" navigation does not carry the
> rc:LinkTarget=iframename parameter forward to the linked report.
> Subsequent clicks break the report out of the IFRAME. The workaround,
> dynamically creating the URL, is cumbersome and limits report portability.
> In the Navigation tab (Parameters button), I tried to set rc:LinkTarget as
> a parameter with iframename as the value, which produced a compilation
> error, that parameter names must be CLR-compliant. This is true except for
> rs: and rc: parameters. Please consider allowing a report to compile and
> publish with rs: and rc: parameters specified in the Jump to... links.
> Thanks,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
>