CODEWITHSUNDEEP
sql-serverstringdynamicreturn-valuedynamic-sql
Abdullah Ilgaz
Abdullah Ilgaz

Reputation: 729

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

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.

Upvotes: 1

Views: 1056

Answers (1)

Abdullah Ilgaz
Abdullah Ilgaz

Reputation: 729

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)

Upvotes: 1

Related Questions