CODEWITHSUNDEEP
t-sqlsp-executesql
Andrew
Andrew

Reputation: 203

result of sp_executesql in a variable

How can I get the output of the below query in a variable without temp table?

DECLARE @Q1 NVARCHAR(300)
DECLARE @Q2 NVARCHAR(300)
DECLARE @Q3 NVARCHAR(300)

SET @Q1 = 'SELECT ' +' ' + @formfieldpath 
SET @Q2 = 'FROM [TestDatabase].[details] WHERE id ' + '=''' + CAST(@id AS VARCHAR(10)) + '''';
SET @Q3 = @Q1 +' '+ @Q2 

PRINT @Q3

EXEC sp_executesql @Q3

Tried 'How to get sp_executesql result into a variable?' and not able to get the results.

Upvotes: 0

Views: 170

Answers (1)

Thom A
Thom A

Reputation: 95554

Assuming that you get a singleton value from your dynamic statement:

DECLARE @ID int, --Is set somewhere
        @YourVariable nvarchar(30), --Use an appropriate data type
        @formfieldpath sysname; --Is set somewhere

DECLARE @SQL nvarchar(MAX);

--I assume that the name [TestDatabase].[details] is wrong, as [TestDatabase] would be the name of the schema, not the database,
--and I ASSUME you haven't foolishy created a schema called "TestDatabase"
SET @SQL = N'SELECT @YourVariable = ' + QUOTENAME(@formfieldpath) + N' FROM dbo.Details WHERE id = @id'; 

--Use the correct datatype for @YourVariable
EXEC sys.sp_executesql @SQL, N'@id int, @YourVariable nvarchar(30) OUTPUT', @id, @YourVariable OUTPUT;

Never inject unsanitised values into a dynamic SQL statement. SQL injection is a huge problem that should have stopped existing over a decade ago. Dos and Don'ts of Dynamic SQL

Upvotes: 3

Related Questions