动态 SQL 将局部变量读取为表变量？
这不是关于使用表变量 - 这是关于使用局部变量在动态 SQL 游标中携带数据库地址，理论上它的工作方式如下： -- 假设已声明全局变量@sql、AnalysisLocation 和@sp_executeSql。
ALTER PROCEDURE [dbo].[sp_AggregateCompliance_Report]
@clientID int,
@InvScrDBLocation nvarchar(250),
@JoinFilter nvarchar(max) = '',
@Criteria nvarchar(max) = '',
@Year int = NULL
as
declare @sql nvarchar(4000)
set @sql = '
IF EXISTS (SELECT * FROM sys.tables WHERE name = ''tmp_Aggregate_Compliance_counts'')
TRUNCATE TABLE tmp_Aggregate_Compliance_counts
ELSE
CREATE TABLE tmp_Aggregate_Compliance_counts (
pfc_fk_prv_pkid int,
RxYear int,
RxMonth int,
Compliance decimal (6,5))
' print @sql EXEC sp_executesql @sql
SET @Criteria = isnull(case when @Criteria like 'WHERE %' then 'AND '+substring(@criteria,7,len(@criteria)-6) else @Criteria end ,'')
SET @Year = isnull(@year, year(getdate())-1)
set @sql = '
DECLARE @fk_cli_pkid INT
, @ServerAndDB_for_pfcAppended nvarchar(100)
DECLARE client_set CURSOR FOR
SELECT DISTINCT mtx.fk_cli_pkid, SettingValue+ ''.dbo.pfc_appended''
FROM mtx_ComplianceAndEarlyRefill_tracking AS mtx
JOIN prola7.Invoice_Screens.dbo.client_definition AS def
ON mtx.fk_cli_pkID = def.fk_cli_pkid
AND fk_lkSettings_pkID = 45
AND RecordStatus = 1
OPEN client_set
FETCH next FROM client_set
INTO @fk_cli_pkid, @ServerAndDB_for_pfcAppended
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO tmp_Aggregate_Compliance_counts (pfc_fk_prv_pkid, RxYear, RxMonth, Compliance)
SELECT pfc.pfc_fk_prv_pkid
, year(mtx.pfc_dateofservice) AS RxYear
, 0 AS RxMonth
, cast(mtx.Compliance as decimal (6,5))
FROM mtx_ComplianceAndEarlyRefill_tracking AS mtx
LEFT OUTER JOIN @ServerAndDB_for_pfcAppended AS pfc
ON mtx.pp_clientfile = pfc.pp_clientfile
AND mtx.pp_mirror_pkid = pfc.pp_mirror_pkid
AND mtx.fk_cli_pkid = @fk_cli_pkid
'+@JoinFilter+'
WHERE pfc.pfc_status = 0
AND year(mtx.pfc_dateofservice) = '+cast(@Year as nvarchar)+'
'+@Criteria+'
GROUP BY pfc.pfc_fk_prv_pkid, year(mtx.pfc_dateofservice)
FETCH next FROM client_set
INTO @fk_cli_pkid, @ServerAndDB_for_pfcAppended
END
CLOSE client_set
DEALLOCATE client_set
' print @sql EXEC sp_executesql @sql
这在编译动态代码时不会产生语法错误，但是在调用此过程时：Msg 1087，Level 15，State 2，Line 27 必须声明表变量“@ServerAndDB_for_pfcAppished”。
当我使用这种类型的结构将位置变量作为全局变量从过程外部传递时，它正确地接受它，但是作为局部变量，它似乎默认假设我打算将它作为表变量。
我不想创建表变量。这是一个不可能的结构吗？
This is NOT about using a table variable - this is about using a local variable to carry a db address within a dynamic SQL cursor which theoretically would work as follows:
-- Assume the global variables @sql, AnalysisLocation, and @sp_executeSql have been declared.
ALTER PROCEDURE [dbo].[sp_AggregateCompliance_Report]
@clientID int,
@InvScrDBLocation nvarchar(250),
@JoinFilter nvarchar(max) = '',
@Criteria nvarchar(max) = '',
@Year int = NULL
as
declare @sql nvarchar(4000)
set @sql = '
IF EXISTS (SELECT * FROM sys.tables WHERE name = ''tmp_Aggregate_Compliance_counts'')
TRUNCATE TABLE tmp_Aggregate_Compliance_counts
ELSE
CREATE TABLE tmp_Aggregate_Compliance_counts (
pfc_fk_prv_pkid int,
RxYear int,
RxMonth int,
Compliance decimal (6,5))
' print @sql EXEC sp_executesql @sql
SET @Criteria = isnull(case when @Criteria like 'WHERE %' then 'AND '+substring(@criteria,7,len(@criteria)-6) else @Criteria end ,'')
SET @Year = isnull(@year, year(getdate())-1)
set @sql = '
DECLARE @fk_cli_pkid INT
, @ServerAndDB_for_pfcAppended nvarchar(100)
DECLARE client_set CURSOR FOR
SELECT DISTINCT mtx.fk_cli_pkid, SettingValue+ ''.dbo.pfc_appended''
FROM mtx_ComplianceAndEarlyRefill_tracking AS mtx
JOIN prola7.Invoice_Screens.dbo.client_definition AS def
ON mtx.fk_cli_pkID = def.fk_cli_pkid
AND fk_lkSettings_pkID = 45
AND RecordStatus = 1
OPEN client_set
FETCH next FROM client_set
INTO @fk_cli_pkid, @ServerAndDB_for_pfcAppended
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO tmp_Aggregate_Compliance_counts (pfc_fk_prv_pkid, RxYear, RxMonth, Compliance)
SELECT pfc.pfc_fk_prv_pkid
, year(mtx.pfc_dateofservice) AS RxYear
, 0 AS RxMonth
, cast(mtx.Compliance as decimal (6,5))
FROM mtx_ComplianceAndEarlyRefill_tracking AS mtx
LEFT OUTER JOIN @ServerAndDB_for_pfcAppended AS pfc
ON mtx.pp_clientfile = pfc.pp_clientfile
AND mtx.pp_mirror_pkid = pfc.pp_mirror_pkid
AND mtx.fk_cli_pkid = @fk_cli_pkid
'+@JoinFilter+'
WHERE pfc.pfc_status = 0
AND year(mtx.pfc_dateofservice) = '+cast(@Year as nvarchar)+'
'+@Criteria+'
GROUP BY pfc.pfc_fk_prv_pkid, year(mtx.pfc_dateofservice)
FETCH next FROM client_set
INTO @fk_cli_pkid, @ServerAndDB_for_pfcAppended
END
CLOSE client_set
DEALLOCATE client_set
' print @sql EXEC sp_executesql @sql
This creates no syntax errors when compiling the dynamic code, however when calling this procedure: Msg 1087, Level 15, State 2, Line 27
Must declare the table variable "@ServerAndDB_for_pfcAppended".
When I use this type of structure passing the location variable in as a global variable from outside the procedure it accepts it correctly, however as a local variable it seems to default to presuming I intend it to be a table variable.
I do NOT want to create a table variable. Is this an impossible structure?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
该错误是由于您尝试使用参数化表名而引起的。这是不可能的，每当表名应该是参数时，就会使用动态查询，基本上是这样的：
我认为，在您的情况下，应该将游标从动态查询中取出，除了使用参数化的部分表名。像这样的事情可能应该做：
The error is caused by the fact that you are attempting to have a parametrised table name. This is not possible, and whenever a table name should be a parameter, a dynamic query is used, basically like this:
I think, in your situation the cursor should be taken out of the dynamic query, except for the part that uses the parametrised table name. Something like this should probably do: