CODEWITHSUNDEEP
sqlsql-serveropenquery
Urwish Patel
Urwish Patel

Reputation: 11

Assign dynamic Openquery to a variable in SQL Server

I have a stored procedure with the following dynamic query

ALTER PROCEDURE [dbo].[Del_AE_Update] 
      @uid nvarchar(MAX)
AS
BEGIN
    Declare @AEname varchar(MAX);
    Declare @sqlquery varchar(MAX);

    Set @sqlquery = ('Select name FROM OPENQUERY(CRM_MYSQL,''Select name From tscrm_prod.user_info where uid='+@uid+''')')  

    Select @sqlquery
END

However I am getting the value in name. I want it to be assigned to @sqlquery because I need to use it in next part of the procedure.

This is how my results look.

name= Dallas-BDD

@sqlquery = 'Select name FROM OPENQUERY...

Upvotes: 0

Views: 1488

Answers (1)

JohnG
JohnG

Reputation: 272

You have to execute the query string (@sqlquery) to be able to get the value of your query. To be able to get the result of a dynamic query into a variable, you need to use sp_executesql with one input and one output parameters.

There are so many examples on the web.

it will look something like this: (This is a simplified version to give you an idea)

DECLARE @ParmDefinition nvarchar(500);
Declare @sqlquery nvarchar(4000);
Declare @name varchar(100);

Set @sqlquery =('Select @name= UserName From tmp_users where userid=@uid') 

    SET @ParmDefinition = N'@uid varchar(max),
        @name nvarchar(25) OUTPUT';
    EXECUTE sp_executesql
        @sqlquery
        ,@ParmDefinition
        ,@uid = 1
        ,@name = @name OUTPUT;
Select @name -- which you will be able to use to continue in your SP

Upvotes: 1

Related Questions