Stored Proc Question

Dr.Silly

Hi,

If I have a stored proc which outputs say an integer.
How do I use the output of that as an input parameter into another query.

for e.g

select * from table_1 where table_1.mycolumn
= "Result from ...execute stored procedure"


Many thanks

totoal

Dr.Silly wrote: »

Hi,

If I have a stored proc which outputs say an integer.
How do I use the output of that as an input parameter into another query.

for e.g

select * from table_1 where table_1.mycolumn
= "Result from ...execute stored procedure"


Many thanks

Guessing you would run the stored proc and store value in a variable and then use the variable in the Where clause...
or am I missing out on something?

fasty

I don't know if you can put it in the actual query and it might be different for other databases. I use MS SQL and would do the following...

declare @proc_return_value int
exec @proc_return_value = MyProc
select * from SomeTable where SomeColumn = @proc_return_value

I don't think you can call a stored proc inline like you've described.

DonnieBrasco

a function would do the business

amen

you could also use the output parameter

BeautifulLoser

You could do something like:
1. Create a temp table for the result of the sproc
2. Exec the stored procedure into your temp table
3. Inner join from your table to the temp table.

CREATE TABLE #Temp
(
 SprocResult int
)

INSERT #Temp
Exec MySproc 

Select 
*
From 
table_1
Inner Join #Temp on #Temp.SprocResult = table_1.mycolumn

If your stored procedure only returns a single int it might be best rewritten as a User Defined Function (as suggested)

Declare @MyInt as int

Set @MyInt = dbo.YourUserDefinedFunctionName()

Select 
*
From 
table_1
Where
table_1.mycolumn = @MyInt

fasty

BeautifulLoser,

If he makes a user defined function, then

select * from SomeTable where SomeColumn = Somefunction();

will work.

Dr.Silly

thanks chaps

BeautifulLoser

fasty wrote: »

BeautifulLoser,

If he makes a user defined function, then

select * from SomeTable where SomeColumn = Somefunction();

will work.

This will definitely work but I thought that having a udf in the Where clause might be less efficient..I could very well be wrong..

amen

Beautiful user why go to the expense of creating a temporary table?

voxpop

Functions can be pretty bad performance wise. In a large select they will be called for each row the select returns.

Better setting the value to a variable and then using that.


Edit: Actually found this article ->sql article - suggests that using a table-valued UDF or a scalar UDF would avoid most of the performance drawbacks