executing SP

20 Aug 2005 2:09 PM

simon

I have stored procedure with parameters.

Can I exec stored procedure somehow with result set of select statement, for
example:

exec dbo.myProcedure (select par1,par2,par3 FROM myTable)

Or I must declare each parameter:

declare @par1 int,@par2 int,@par3 int

SELECT @par1= par1,@par2=par2,@par3=par3 FROM myTable

and then exec my procedure:

exec dbo.myProcedure @par1,@par2,@par3

In real example I have a lot of columns and declaring many of them just to
execute another SP is not so pleasent.

lp,S

20 Aug 2005 4:10 PM

Anith Sen

>> Can I exec stored procedure somehow with result set of select statement,

No, a SELECT statement returns a set of rows. A Stored procedure cannot take
a set of rows for its parameter -- it has to be scalar values. So you have
to explicitly assign individual variables to pass them as parameters.

--
Anith

20 Aug 2005 7:56 PM

Louis Davidson

To add to what Anith said, build yourself a query from the
information_schema.columns view to build the parm list, especially if you do
this often.

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP

Show quote

"simon" <simon.zu***@iware.si> wrote in message
news:SPGNe.1586$cE1.227654@news.siol.net...
>I have stored procedure with parameters.
>
> Can I exec stored procedure somehow with result set of select statement,
> for example:
>
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
>
> Or I must declare each parameter:
>
> declare @par1 int,@par2 int,@par3 int
>
> SELECT @par1= par1,@par2=par2,@par3=par3 FROM myTable
>
> and then exec my procedure:
>
> exec dbo.myProcedure @par1,@par2,@par3
>
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
>
> lp,S
>

20 Aug 2005 9:05 PM

John Bell

Hi

If you want to create the procedure you could run the query as a SELECT INTO
statement (possibly with WHERE 1=0 to stop any rows being returned!) you
will get a table with the column names and datatypes. This can be scripted
in the object browser into a window and edited (you may want to remove
collations and add @ to the names!)

If you already have the procedure definition then look at
INFORMATION_SCHEMA.COLUMNS as already suggested.

John

Show quote

"simon" <simon.zu***@iware.si> wrote in message
news:SPGNe.1586$cE1.227654@news.siol.net...
>I have stored procedure with parameters.
>
> Can I exec stored procedure somehow with result set of select statement,
> for example:
>
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
>
> Or I must declare each parameter:
>
> declare @par1 int,@par2 int,@par3 int
>
> SELECT @par1= par1,@par2=par2,@par3=par3 FROM myTable
>
> and then exec my procedure:
>
> exec dbo.myProcedure @par1,@par2,@par3
>
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
>
> lp,S
>