Tuesday, March 24, 2009

[t-sql] Catch return SELECT value of Store Procedure

I have a store procedure which use the SELECT statement to return the recordset to caller.
How do I catch the data into variable or temp-table? The value will be used for next process.

Answer: Use OpenQuery() to Local Link Server (you must create the link server first)

Sample store procedure which return the recordset:
    CREATE PROCEDURE usp_test
( @pInp varchar(10)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT 'aaaa' AS ret_val
END
GO

To create link server for local server:
    IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'loopback')
EXEC sp_dropserver 'loopback'
GO
EXEC sp_addlinkedserver
@server = N'loopback',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = @@servername
GO

Procedure to catch the value into variable:
    DECLARE @cReturn varchar(1000)
SELECT @cReturn = ret_val FROM openquery(loopback, 'mydb..usp_test ''input''')
PRINT @cReturn

Suppose you have dynamic input parameter into store procedure usp_test() then you must use sp_executesql to execute the command in string.
    DECLARE @cReturn varchar(1000),
@cSql varchar(1000),
@cInput varchar(1000),
@nExec nvarchar(4000)

SET @cInput = '''''a'''''
SET @cSql = '''ttsdb..usp_test ' + @cInput + ''''

SET @nExec = 'SELECT @cReturn = ret_val FROM openquery(loopback, ' + @cSql + ')'
EXEC sp_executesql
@nExec,
N'@cReturn varchar(100) output',
@cReturn output

PRINT @cReturn

Have a lot of fun...