CODEWITHSUNDEEP
sql-serverstored-procedures
Bruno
Bruno

Reputation: 4517

Set a SP return value to a variable in SQL Server

I have a sproc that returns a single line and column with a text, I need to set this text to a variable, something like:

declare @bla varchar(100)
select @bla = sp_Name 9999, 99989999, 'A', 'S', null

but of course, this code doesn't work...

thanks!

Upvotes: 13

Views: 80810

Answers (3)

DiGi
DiGi

Reputation: 2558

DECLARE
  @out INT

EXEC @out = sp_name 'param', 2, ...

More info in T-SQL "EXECUTE" help (Help is from MSSQL 2008 but this works in 2000 too)

Upvotes: 15

Tim C
Tim C

Reputation: 70618

If you are unable to change the stored procedure, another solution would be to define a temporary table, and insert the results into that

DECLARE @Output VARCHAR(100)

CREATE TABLE #tmpTable
(
    OutputValue VARCHAR(100)
)
INSERT INTO #tmpTable (OutputValue)
EXEC dbo.sp_name 9999, 99989999, 'A', 'S', null

SELECT
    @Output = OutputValue
FROM 
    #tmpTable

DROP TABLE #tmpTable

Upvotes: 29

Tim C
Tim C

Reputation: 70618

If the stored procedure is returning a single value you could define one of the parameters on the stored procedure to be an OUTPUT variable, and then the stored procedure would set the value of the parameter

CREATE PROCEDURE dbo.sp_Name
    @In INT,
    @Out VARCHAR(100) OUTPUT

AS
BEGIN
    SELECT @Out = 'Test'
END
GO

And then, you get the output value as follows

DECLARE @OUT VARCHAR(100)
EXEC sp_name 1, @Out OUTPUT
PRINT @Out

Upvotes: 19

Related Questions