通过在T-SQL中从列表中选择SQL变量来执行存储过程，而不使用Cursor

Question

我有一个值列表，我需要变量一个接一个地拾取它并执行下面的命令。我需要在T-SQL中实现如下内容。没有游标可以吗?

SET NOCOUNT ON;
DECLARE @IPA VARCHAR(10)
FOR @IPA IN ['ADV, 'AC','AHA','ALPEB','AG','APCWEB]

IF OBJECT_ID('[ESProcess].[dbo].[EJ_Test]') IS NOT NULL
DROP TABLE [ESProcess].[dbo].[EJ_Test]

CREATE TABLE [ESProcess].[dbo].[EJ_Test]
(
[PRIM] [varchar](50) NULL,
[CLAIM_ID] [varchar](100) NULL,
[P_CLAIMNO] [varchar](100) NULL,
[Pro] [varchar](3) NULL,
[VALUE] [varchar](50) NULL,
[ErrorCode] [int] NULL,
[DESCRIP] [varchar](500) NULL,
[FILENAME] [varchar](300) NULL
)
INSERT INTO [ESProcess].[dbo].[EJ_Test]
SELECT 
PRIM,a.CLAIM_ID,P_CLAIMNO, Pro, u.VALUE,
NULL AS ErrorCode, NULL AS DESCRIP, [FILENAME] 
FROM
[SPID].[@IPA].[dbo].837_in_1 a
JOIN
[SPID].[@IPA].[dbo].[837_In_U] u ON a.ED_ID = u.M_ID
WHERE
a.CREATEDATE >= '20221001' 
AND u.FNO = '20' 
AND Pro = 'D'
END

Answer 1

这里。请注意，拖放创建表意味着最终您将只拥有来自APCWEB的数据。

SET NOCOUNT ON;
DECLARE @IPA VARCHAR(10)
declare @sql nvarchar(max)
create table #ipas
(
ord int
,ipa varchar(10)
)
insert #ipas(ord,ipa) values
(1,'ADV')
,(2,'AC')
,(3,'AHA')
,(4,'ALPEB')
,(5,'AG')
,(6,'APCWEB')

declare ipa_cursor cursor local fast_forward for
select ipa from #ipas order by ord
open ipa_cursor
while 1=1
begin
fetch next from ipa_cursor into @IPA
if @@fetch_status<>0 break

set @sql=N''+
'IF OBJECT_ID(''[ESProcess].[dbo].[EJ_Test]'') IS NOT NULL
DROP TABLE [ESProcess].[dbo].[EJ_Test]

CREATE TABLE [ESProcess].[dbo].[EJ_Test]
(
[PRIM] [varchar](50) NULL,
[CLAIM_ID] [varchar](100) NULL,
[P_CLAIMNO] [varchar](100) NULL,
[Pro] [varchar](3) NULL,
[VALUE] [varchar](50) NULL,
[ErrorCode] [int] NULL,
[DESCRIP] [varchar](500) NULL,
[FILENAME] [varchar](300) NULL
)
INSERT INTO [ESProcess].[dbo].[EJ_Test]
SELECT 
PRIM,a.CLAIM_ID,P_CLAIMNO, Pro, u.VALUE,
NULL AS ErrorCode, NULL AS DESCRIP, [FILENAME] 
FROM
[SPID].'+quotename(@IPA)+'.[dbo].837_in_1 a
JOIN
[SPID].'+quotename(@IPA)+'.[dbo].[837_In_U] u ON a.ED_ID = u.M_ID
WHERE
a.CREATEDATE >= ''20221001'' 
AND u.FNO = ''20'' 
AND Pro = ''D'''

exec sp_executesql @sql 

end -- cursor while
close ipa_cursor
deallocate ipa_cursor