Using values returned from a Stored Procedure

[Guggly]

Hi! I have a few SELECT StoredProcedures, but it seems I can't use the values they return from another SP. When I refer to it in code I get an "Invalid object name" error.

Can I only access a View to be run by another StoredProcedure, or am I doing something wrong? Sorry if this is a real basic question, but I'm trying to find my feet with using SQL Server as opposed to Access SQL.

Thanks! -- Mike

 

[dbomrrsm]

an example:

create proc MYTESTSP
as
select max(rec_id)
from MYTABLE --Returns result 2
GO
EXEC MYTESTSP   --Returns result 2

declare @Variable nvarchar (15)
Set @Variable = 'EXEC MYTESTSP'
EXEC sp_executesql @Variable --Returns result 2
PRINT @Variable

[blue]DBomrrsm[/blue]

 

[hmckillop]

You cant use stored procedures in SELECT statements, however you can use functions. If you require to use the returned data in a SELECT statement create your PROC as a FUNCTION.
e.g. this example shows a function being used in a SELECT statement only for demo purposes

CREATE FUNCTION ufn_IsInSpecifiedRange
( 
  @pi_Input bigint
)
RETURNS int
AS
BEGIN
	--check if between 1 and ten
	IF @pi_input >=0 AND @pi_Input <=10
	BEGIN
		RETURN 1
	END
	
	RETURN 0
	
END

And to call it you would use

SELECT dbo.ufn_IsInSpecifiedRange(99)
--or


SELECT dbo.ufn_IsInSpecifiedRange(myfield1) As InRange 
FROM MyTable
--where myfield1 is an int column in MyTable

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Guggly]

Thanks guys! But what if my StoredProcedure returns multiple fields and records (a whole dataset). I can't use that as a function, right?

Basically, my SP joins a few tables, performs some calculations and then lists the output using a SELECT statement.
Now, I need to take that SP and with the values that are returned, join with another table and update some records based on the values from the SP.

I suppose I could design the SP to work as a function returning one value at a time, but that would require the SP/function to be called a few thousand times and doesn't seem very efficient to me (or is that the way to do it with SQL?)
If I change the StoredProcedure SELECT statement to a View it works fine, but I'm concerned about performance issues, and more importantly, doing it the right way

Thanks! -- Mike

 

[dbomrrsm]

you can create a temprary table to hold the results of the SP then join the temp table to the others for onward processing.

CREATE TABLE #TEMP(fields.....)
INSERT INTO #TEMP
EXEC MYSTOREDPROC

Then

Update MyExistingTable
SET etc
WHERE MyExistingTable.ID = #TEMP.ID

[blue]DBomrrsm[/blue]

 

[SQLSister]

DBomrrsm's method will certainly work.

But I do need to point out that a function can certainly return a whole dataset. It's called a table-valued function. You then use it in the sp like any other table.

Questions about posting. See faq183-874

 

[hmckillop]

to add on SQLSister's point and my original point, you can still use functions, bu how they are called are slightly different.
A table valued function should be used as if it is a table, as it returns multiple rows this makes sense.

So where a function could be called as shown in my original post, a table valued function should be returned as shown below

SELECT * FROM dbo.ufn_tablevaluedfunctionname

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Guggly]

Lots of good input. Thank you! I'll have to research my options for this! -- Mike

 

[Guggly]

Got it working using a function. Thanks for all your help!