变量中sp_executesql的结果

Question

如何在没有临时表的变量中获取以下查询的输出？

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

尝试"如何将结果放入变量sp_executesql？"但无法获得结果。

Answer 1

假设您从动态语句中获取一个单例值：

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;

切勿将未经净化的值注入动态 SQL 语句。SQL注入是一个巨大的问题，应该在十多年前就停止存在。动态SQL的注意事项