CODEWITHSUNDEEP
sql-server-2008t-sqlstored-procedures
Stiger
Stiger

Reputation: 1199

How to insert value from stored procedure to variable in function in SQL Server

I have a stored procedure which returns a value (ex: GetData)

How can I declare a variable in another function to get this value?

Something like this

Create Function Test
Return Int
Begin
    Declare @data My_Data_Type
    Declare @count int
    SET @data = exec GetData
    Select @count = count(*)
    From @data

    Return @count
End

Please help me !!!!

Upvotes: 1

Views: 8418

Answers (2)

codingbiz
codingbiz

Reputation: 26386

If your stored procedure returns table then do

declare @data as table(col1 int, col2 varchar(50))

insert into @data exec GetData

Select @count = count(*) from @data

If you want to return scalar valus e.g. int, varchar, use OUTPUT parameter

CREATE PROC GetData
(
   @retVal int OUTPUT
)
AS
BEGIN
     SET @retVal = 123

     Return 0
END

Then retrieve it this way

 declare @data int
 EXEC GetData @data OUTPUT

Upvotes: 5

Adriaan Stander
Adriaan Stander

Reputation: 166396

Have a look at Using a Stored Procedure with Output Parameters

A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application.

Also Returning Data by Using OUTPUT Parameters

If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the stored procedure.

Upvotes: 0

Related Questions