0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andrew
  • 183
  • 2
  • 14

1 Answers1

3

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Thanks a lot. it is working as expected. Thanks for the link too. – Andrew Dec 23 '19 at 23:52
  • You're welcome @Andrew . Please do consider marking the answer as the solution, so that future readers know the answer was helpful. You should also do so with your other questions; it's a very important part of the site. – Thom A Dec 24 '19 at 00:34