• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

Execute stored procedure with inputs from SELECT

Is there a way to execute a stored procedure using the results from a select?   I have a stored procedure who's signature takes in four variables I want to be able to call the stored procedure by writing a select to retrieve the four variables, which may return multiple rows.  For example

EXEC sp_TestSP @var1, @var2, @var3, var4
SELECT var1, var2, var3, var4
FROM SPInputs
0
rmariotti
Asked:
rmariotti
1 Solution
 
derekkrommCommented:
declare @var1 int, @var2 int, @var3 int, @var4 int

select @var1=var1, @var2=var2, @var3=var3, @var4=var4 from SPInputs

exec sp_TestSP @var1, @var2, @var3, @var4
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is there a way to execute a stored procedure using the results from a select?
not like that.
how many rows do you get?

so either, you get the procedure into a function, or, if your query returns only 1 row, or you need to do a cursor on the SELECT.


0
 
Scott PletcherSenior DBACommented:
No.  You could modify the sp to process data from a temp table, load the temp table prior to calling the sp, then passing a flag to the stored proc to let it know to process the value in the temp table rather than in the params.  For example:

CREATE TABLE #params (
    var1 VARCHAR(30),
    var2 INT,
    var3 INT,
    var4 DATETIME
    )
INSERT INTO #params
SELECT var1, var2, var3, var4
FROM SPInputs

EXEC sp_TestSP 'UseTemp', -1
0
 
rmariottiAuthor Commented:
If I made it a table-valued UDF, how would I join to it?   Basically, the select can return multiple rows, and each of those rows contains variables that should be passed into the function.  In addition, the function will return multiple rows.  So if the function returns 3 rows per call and the SPInputs table has 2 rows, the SELECT would return a ResultSet of 6 rows...

      SELECT *
      FROM fn_ResultSet(spi.var1, spi.var2, spi.var3, spi.var4)
      CROSS JOIN SPInputs spi
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in sql server 2000, you cannot do that in a single sql.
in sql server 2005, you could do that indeed using the CROSS APPLY:

      SELECT *
      FROM SPInputs spi
      CROSS APPLY dbo.fn_ResultSet(spi.var1, spi.var2, spi.var3, spi.var4) r

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now