CODEWITHSUNDEEP
sql-servert-sqlopenquery
Tim Rose
Tim Rose

Reputation: 45

Unable to return a value from a linked database within stored procedure

I'm trying to write a store procedure (or function if easier) to query a linked database and return a value but massively struggling. Any help greatly appreciated...

What I have tried:

CREATE Proc dbo.Sample (@FolderID as VARCHAR(50),
@FolderName varchar(50) OUTPUT)
AS
BEGIN

DECLARE @ParamaterDefinition NVARCHAR(500)
DECLARE @FolderNameRet VARCHAR(50)
DECLARE @TSQL NVARCHAR(4000)

SET @TSQL = N'SELECT folder_name FROM OPENQUERY(LINKDB, ''SELECT 
        folder_name 
    FROM FolderTable
    where folder_id = '+ @FolderID +' and folder_status != 0'')'
SET @ParamaterDefinition = N'@FolderID as VARCHAR(50),
                    @FolderName VARCHAR(50) OUTPUT'
EXEC SP_EXECUTE @TSQL,
@ParamaterDefinition,
@FolderID,
@FolderName = @FolderNameRet output

SELECT @FolderNameRet AS "Output"
SELECT @FolderID AS "FolderID1"
SELECT @TSQL AS "Script"
SELECT @FolderName AS "FolderName1"
SELECT @ParamaterDefinition AS "Parameters"

RETURN @FolderNameRet 
END

Using the SELECT statements at the end to see each variable, and the script that is generated is fine and can run this separately to get my desired value. However @FolderNameRet is still coming up NULL.

I feel like the issue is me trying to return a value incorrectly but every method I have tried is failing.

i.e. Don't think I can set the return value within the dynamic script as this needs to be in a string format to run as part of the OPENQUERY statement. Also tried using EXEC @FolderNameRet = SP_EXECUTE @TSQL but this is also returning NULL.

Please help!

Upvotes: 2

Views: 671

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175884

You could try to assign value to @FolderName variable:

SET @TSQL = N'SELECT @FolderName = folder_name FROM OPENQUERY(LINKDB, ''SELECT 
        folder_name 
    FROM FolderTable
    where folder_id = '+ @FolderID +' and folder_status != 0'')'
SET @ParamaterDefinition = N'@FolderID as VARCHAR(50),
                    @FolderName VARCHAR(50) OUTPUT';

Also RETURN for stored procedure is for status INT not VARCHAR(50).

Upvotes: 1

Related Questions