0

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...
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Urwish Patel
  • 11
  • 8

1 Answers1

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
JohnG
  • 272
  • 2
  • 14
  • @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](https://msdn.microsoft.com/en-us/library/ms188001.aspx) – JohnG Nov 16 '15 at 17:09
  • 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. – Urwish Patel Nov 16 '15 at 17:15
  • @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. – JohnG Nov 16 '15 at 18:02