sql-server - 如何在sql中使用exec字符串分配变量？

Question

我想用“exec(字符串)”返回的内容分配一个变量，但在语法上遇到了困难。下面是一些工作代码...

declare @iGeographyLevel int = 2
declare @iGeographyLevelID int = 64
declare @sGeographyName varchar(30)

declare @sSQL nvarchar(max)

set @sSQL = '
select Name
    from GeographyLevel'+ cast(@iGeographyLevel as varchar(5))+'
    where GeographyLevel'+ cast(@iGeographyLevel as varchar(5)) + 'ID = '+ cast(@iGeographyLevelID as varchar(5))

exec (@sSQL)

我想做一些类似的事情

set @sGeographyName  = exec (@sSQL)

Answer 1

sp_executesql是最好的方法。

DECLARE @iGeographyLevel INT = 2
DECLARE @iGeographyLevelID INT = 64
DECLARE @sGeographyName VARCHAR(30)
DECLARE @sSQL NVARCHAR(max)

SET @sSQL = '
SELECT @sGeographyName = Name
FROM   GeographyLevel' + cast(@iGeographyLevel AS VARCHAR(5)) + '
WHERE  GeographyLevel' + cast(@iGeographyLevel AS VARCHAR(5)) + 'ID = @iGeographyLevelID'

EXEC sp_executesql
  @sSQL,
  N'@iGeographyLevelID INT, @sGeographyName VARCHAR(30) OUTPUT',
  @iGeographyLevelID,
  @sGeographyName OUTPUT

SELECT @sGeographyName 

这里对动态 SQL 的要求有点臭。不知道为什么您不只有一个带有 Level 列的 Geography 表(或者如果表必须是单独的，则可能是分区 View )变量名称的匈牙利表示法是 widely discouraged这些天。