SQL Server T-SQL:使用存储过程自动重新分配SQL报告

Question

目标：创建一个自动化此报告的存储过程，以便当运行execute NameOfStoredProc时，它执行所有3个块并在块3中返回查询。<<<<<<<<<<<<<<<<<<<<<<

为了进入表，我希望它基于getdate()。

(我没有发布实际的表元素和记录，但是如果需要，我可以弥补b/c实际数据是敏感的(

• 数据库：FY1516
• 从表：v_all_claim(实际上是视图(
• 进入表：March2017_Payments

这是我手动执行以生成报告的代码。

块1：

--creates payment table
SELECT Recipient_ID, DOP, Provider_ID, program_code, poverty_code 
INTO FY1516..March2017_Payments
FROM FY1516..v_all_Claim
WHERE amount <> 0 
  AND DOP BETWEEN '20170301' AND '20170331'

块2：

-- add one column to the table created in block 1, sets default value to '' and update to Y 
-- if certain constraints are met
ALTER TABLE FY1516..March2017_Payments
    ADD TITLE21_Flag varchar(1);
GO 
UPDATE FY1516..March2017_Payments
SET TITLE21_Flag = ''
GO 
UPDATE FY1516..March2017_Payments
SET TITLE21_Flag = 'Y'
WHERE program_code IN ('A', 'B', 'C')

块3 带有Select语句，该语句被复制到Excel中：

SELECT * 
FROM FY1516..March2017_Payments

到目前为止，我的尝试：

• @start和@end用于between @start and @end
• @previousMonth给出了前一个月的前3个字母
• @previousMonthYear给出了上个月的Yyyy

希望@previousMonth +@previousMonthYear +"_Payments"可以是tablename

USE FY1516
CREATE PROCEDURE NameOfStoredProc
AS
    DECLARE @start VARCHAR(8), @end VARCHAR(8),
            @previousMonth VARCHAR(3), @previousMonthYear VARCHAR(4);
    SET @start = CONVERT(VARCHAR(8), DATEADD(MONTH, DATEDIFF(MONHT, 0, GETDATE()) - 1, 0), 112)
    SET @end = CONVERT(VARCHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1), 112)
    SET @previousMonth = LEFT(DATENAME(MONTH, DATEADD(MONTH, -1, GETDATE())), 3)
    SET @previousMonthYear = YEAR(DATEADD(MONTH, -1, GETDATE()))

Answer 1

您可以将块1和块2组合到一个语句中：

--creates payment table
Select Recipient_ID
, DOP
, Provider_ID
,program_code
,poverty_code 
,TITLE21_Flag = CASE WHEN program_code IN ('A','B','C') THEN 'Y' ELSE '' END
INTO FY1516..March2017_Payments
FROM FY1516..v_all_Claim
WHERE amount <> 0 and DOP between '20170301' and '20170331'

然后，在您的Proc中，您可以使用动态SQL创建表。这是一个例子：

Create procedure NameOfStoredProc
AS
declare @start varchar(8)
, @end varchar(8)
,@previousMonth varchar(3)
,@previousMonthYear varchar(4);
set @start = convert(varchar(8),dateadd(month, datediff(month,0,getdate())-1,0),112)
set @end = convert(varchar(8),DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1),112)
set @previousMonth =  left(datename(month, dateadd(month,-1,getdate())), 3)
set @previousMonthYear = year(dateadd(month,-1,getdate()))
DECLARE @SQLString NVARCHAR(MAX) = 'CREATE TABLE ' + @previousMonth +@previousMonthYear +'_Payments (ColA int)'
EXECUTE sp_executesql @SQLString

您需要用实际的列名和数据类型替换(ColA int)。

编辑：

这是一个示例，其中包含1/2块中的块中的proc。它首先检查表的存在，然后运行适当的选择查询。

CREATE PROCEDURE NameOfStoredProc
AS
begin
declare @start varchar(8)
, @end varchar(8)
,@previousMonth varchar(3)
,@previousMonthYear varchar(4);
set @start = convert(varchar(8),dateadd(month, datediff(month,0,getdate())-1,0),112)
set @end = convert(varchar(8),DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1),112)
set @previousMonth =  left(datename(month, dateadd(month,-1,getdate())), 3)
set @previousMonthYear = year(dateadd(month,-1,getdate()))
DECLARE @SQLString NVARCHAR(MAX) = 
'IF OBJECT_ID('''+@previousMonth +@previousMonthYear +'_Payments'', ''U'') IS NOT NULL 
BEGIN
    print 1
    INSERT INTO FY1516..'+ @previousMonth +@previousMonthYear +'_Payments
    Select Recipient_ID
    , DOP
    , Provider_ID
    ,program_code
    ,poverty_code 
    ,TITLE21_Flag = CASE WHEN program_code IN (''A'',''B'',''C'') THEN ''Y'' ELSE '''' END
    FROM FY1516..v_all_Claim
    WHERE amount <> 0 and DOP between ''20170301'' and ''20170331''
END
ELSE
BEGIN
print 2
    Select Recipient_ID
    , DOP
    , Provider_ID
    ,program_code
    ,poverty_code 
    ,TITLE21_Flag = CASE WHEN program_code IN (''A'',''B'',''C'') THEN ''Y'' ELSE '''' END
    INTO FY1516..'+ @previousMonth +@previousMonthYear +'_Payments
    FROM FY1516..v_all_Claim
    WHERE amount <> 0 and DOP between ''20170301'' and ''20170331''
END
'
EXECUTE sp_executesql @SQLString
SET @SQLString = 'SELECT * FROM '+@previousMonth +@previousMonthYear +'_Payments'
EXECUTE sp_executesql @SQLString
END