Query that calls stored procedures 2

[juls]

How can I return the results of a stored procedure into a query?&nbsp;&nbsp;For example, I have 4 stored procedures that take in variables and return a calculated value.&nbsp;&nbsp;I want to select every row on a table, pass in the variables to the stored procedures and output the calculated values as part of the select statement:<br>SELECT t.tnum, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t.tdate,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp_01(t.tnum, t.tdate),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp_02(t.tnum, t.tdate),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp_03(t.tnum, t.tdate),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp_04(t.tnum, t.tdate)<br>FROM tablea t<br><br>The above select does not work for me.&nbsp;&nbsp;I ended up creating a cursor and selecting tnum and tdate into it.&nbsp;&nbsp;Then I looped through the cursor and called the stored procedures for each row in the cursor.&nbsp;&nbsp;I inserted the result into a temporary table and then did a SELECT * FROM TEMPTABLE.<br>This method is very slow - it runs FOREVER.&nbsp;&nbsp;Is there a better way to do this?<br>I have worked with Oracle databases and in Oracle you can create a function which would call the stored procedure and return a value.&nbsp;&nbsp;Then, in the query, you can call the function.&nbsp;&nbsp;Is there anything comparable to this in MS SQL7?

 

[Jerrycurl]

Have you tried assigning the result of the stored procedure to a variable and then including the variable in the select statement?

 

[juls]

I don't believe that would work.&nbsp;&nbsp;I want to get the result for every row in the table. So, for instance, if there were 10000 rows in tablea the select statement should return 10000 rows with the calculations for each tnum and tdate in the table.

 

[clawton]

How complicated are your stored procedures ?<br><br>If they are simple calculations, could they be rewritten as views that work out the new values based on the current two input fields.&nbsp;&nbsp;A simple select on the view may then suffice.<br><br>Chris.

 

[foxdev]

I was trying to find an example in BOL of using <i>EXEC sp_name</i> as part of a SELECT statement, because I thought I had seen that before, but I couldn't find it.<br><br>So the easy answer is: wait for SQLS2K.&nbsp;&nbsp;It will (finally) support user-defined functions, which is probably what you want. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[MapMan]

How's this?&nbsp;&nbsp;Here's a snippet provided by Microsoft...<br><br>&lt;tt&gt;<br>CREATE PROCEDURE dbo.mathtutor<br>@m1 smallint,<br>@m2 smallint,<br>@result smallint OUTPUT -- This is needed for use in other SPs<br>AS<br>SET @result = @m1 * @m2<br>GO<br>&lt;/tt&gt;<br>-- Now let's use that SP in another SP or Query<br>&lt;tt&gt;<br>Declare @answer smallint<br>EXECUTE mathtutor 5,6 , @answer OUTPUT -- this passes the parameters<br>-- into the mathtutor SP and<br>-- stores the result in @answer<br>SELECT 'The result is: ', @answer -- This shows the result.<br><br>&lt;/tt&gt; <br>Your output should say:<br>The result is: 30<br>&nbsp;<br>All you'd have to do with this, is repeat the EXECUTE against the other SP's.<br><br>HTH,<br><br>MapMan<br>

 

[juls]

Thanks everyone for the help but I think foxdev is right, I <br>think I have to wait for the implementation of functions (I didn't know SQLS2K was going to have them).<br>JerryCurl and MapMan your suggestion actually works but I still need the cursor for my initial select statement which is returning approx 14400 rows and then execute the stored procedures for each row.&nbsp;&nbsp;After executing the stored procedures I do a SELECT using the variables.<br>I'm getting the results I need but it is slow.<br>

 

[ColinM]

You could try something like this.<br><br>CREATE TABLE Sp1<br>(<br>&nbsp;&nbsp;&nbsp;&nbsp;ID int IDENTITY(1,1),<br>&nbsp;&nbsp;&nbsp;&nbsp;Data varchar(100),<br>)<br>INSERT sp1<br>EXEC sp_01(t.tnum, t.tdate)<br><br>Do that another 3 times, for the other SPs, then you'll have a large where clause linking them all together.<br><br>SELECT t.tnum, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t.tdate,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp1,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp2,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp3,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp4<br>FROM tablea t<br>WHERE sp1.ID = sp2.ID AND sp1.ID = sp3.ID etc....<br>