[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...