Assign dynamic Openquery to a variable in SQL Server

Question

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

Yes I need to know how to do that exactly. I am totally unaware of doing it syntactically. — Urwish Patel
– Urwish Patel, Commented Nov 16, 2015 at 17:05

JohnG · Accepted Answer · 2015-11-16 18:11:25Z

1

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

edited Nov 16, 2015 at 18:11

answered Nov 16, 2015 at 16:51

JohnG

2722 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JohnG Over a year ago

@Urwish Patel, What I put there needs to be worked on. It was just to give you a push in the right direction. check sp_executesql

Urwish Patel Over a year ago

I understand that, but I am using this for the first time. And have no idea syntactically how to do it. Been stuck on it for 2 days.

JohnG Over a year ago

@Urwish Patel, I made some modifications to my answer... which can give you a better idea... I tested it and it worked with a temp table I created.

Collectives™ on Stack Overflow

Assign dynamic Openquery to a variable in SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related