Getting String value of Dynamic SQL into Select query in SQL Server

Question

I need to reach a return value from Dynamic SQL. I execute select query as string with sp_executesql. I have to reach results. Here is sql query:

DECLARE @dynsql nvarchar(max)  
SET @dynsql = 'select @InvoiceNo=InvoiceNo from '+ QUOTENAME(@DynDB) +'.[dbo].[TableName] where UserID = '+ 
        cast(@UserID as nvarchar) 
EXEC sp_executesql @dynsql

Problem: This @dynsql is string value and I have to reach a value in this sql query.

Abdullah Ilgaz · Accepted Answer · 2015-11-28 12:08:42Z

1

The best solution is:

We can add our output parameter to query then we should add it to execute line like this:

DECLARE @dynsql nvarchar(max)  
SET @dynsql = 'select @InvoiceNo=InvoiceNo from '+ QUOTENAME(@DynDB) +'.[dbo].[TableName] where UserID = '+ 
        cast(@UserID as nvarchar) 
EXEC sp_executesql @dynsql, N'@InvoiceNo int OUTPUT',@InvoiceNo = @InvoiceNo OUTPUT
select @InvoiceNo as InvoiceNo

And the result is: InvoiceNo = 11111 (int value)

answered Nov 28, 2015 at 12:08

Abdullah Ilgaz

7391 gold badge18 silver badges40 bronze badges

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

1 Comment

tomosius Over a year ago

This is awesome.

Collectives™ on Stack Overflow

Getting String value of Dynamic SQL into Select query in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related