SQL与MySQL 链接服务器openquery的带变量和可变参数与拼接语句模板的while循环语句的TRY和CATCH的捕捉错误的SQL查询实例

链接服务器openquery的带变量和可变参数与拼接语句模板的while循环语句的TRY和CATCH的捕捉错误的SQL查询实例

一、declare 定义变量 set 设置起始值（DECLARE与SET）

/*
埋词效果评估-BI-埋词近七天的listing出单率
跨表查询，用智能刊登的原表查询
循环迭代
*/
DECLARE @interval INT = 7;
DECLARE @maxLoop INT;
DECLARE @execSql VARCHAR(1000);
DECLARE @startDate DATE;
/*
设置参数和变量
*/
SET @startDate = (SELECT MAX(t.baseLine) FROM(SELECT ISNULL(MAX(lc.BuriedDate),'2000-01-01') baseLine FROM dbo.BuriedListing_ConversionRate lc (NOLOCK) UNION SELECT '2023-05-01' AS baseLine) t)
/*
根据表格日期，查找起始日期值。
在表格中查找数据的最大日起，若表格为空，则默认填补'2000-01-01'.
上述最大日期与指定日期'2023-05-01'的最大值则为开始日期
*/
SET @maxLoop = (SELECT DATEDIFF(DAY, @startDate, DATEADD(DAY, -@interval, GETDATE())));
PRINT @startDate
PRINT @maxLoop

二、计算循环次数准备开始循环

SET @maxLoop = (SELECT DATEDIFF(DAY, @startDate, DATEADD(DAY, -@interval, GETDATE())));

三、SQL的WHILE循环语句（while）

while @maxLoop > 0
begin
-- 循环主体语句
set @maxLoop = @maxLoop - 1
end

四、OPENQUERY的链接服务器带参数拼接语句

附录：
1.SQL的转义符为’。拼接时，两个 ’ 即 ‘’ 表示为 ‘，四个 ’ ,即 ‘’’’ 表示 ‘’
2.数字类型的内容需要转成字符串类型

/*
SET @execSql ='
	〖insert语句和字段的SQL〗  
SELECT * FROM OPENQUERY([LINK],'
SET @execSql=@execSql +    '    ''
	〖链接服务器的原SQL或者MySQL语句。需要参数的地方用 ' 和 + 号进行灵活扩展〗
'' ) '
;
*/

SET @execSql ='
INSERT INTO dbo.BuriedWordListingVv
(
    OrderSourceID,
    ItemID,
    Date
)  SELECT * FROM OPENQUERY([LINK],'
SET @execSql=@execSql +    '    ''select
        ebq.OrderSourceId
        , ebq.ProductID
        , date_format(date_sub(now(), interval 18 day), "%Y-%m-%d")
from m_ebay_buriedwords_queue ebq
where ebq.date >= date_format(date_sub(now(), interval ' + STR(@maxLoop+@interval-1) + ' day), "%Y%m%d")+0
        and ebq.date < date_format(date_sub(now(), interval ' + STR(@maxLoop+@interval-2) + ' day), "%Y%m%d")+0
        and ebq.status=1
        and ebq.type=202
group by ebq.OrderSourceId
        , ebq.ProductID '' ) '
;
PRINT @execSql;
EXEC(@execSql);

五、TRY和CATCH捕捉错误(try和catch)

begin try
未发生错误时执行的语句
end try
begin catch
发生错误时执行的语句
end catch

BEGIN TRY
	-- 未发生错误时执行的语句
END TRY
BEGIN CATCH
	-- 发生错误时执行的语句
        PRINT '--- 有问题 ---'
        PRINT CONVERT(DATETIME2, DATEADD(DAY, -(@maxLoop+@interval-1), GETDATE()),120);
        PRINT '--- ------ ---'
END CATCH

六、总体代码

/*
埋词效果评估-BI-埋词近七天的listing出单率
跨表查询，用智能刊登的原表查询
循环迭代
*/
DECLARE @interval INT = 7;
DECLARE @maxLoop INT;
DECLARE @execSql VARCHAR(1000);
DECLARE @startDate DATE;
/*
设置参数和变量
*/
SET @startDate = (SELECT MAX(t.baseLine) FROM(SELECT ISNULL(MAX(lc.BuriedDate),'2000-01-01') baseLine FROM dbo.BuriedListing_ConversionRate lc (NOLOCK) UNION SELECT '2023-05-01' AS baseLine) t)
/*
根据表格日期，查找起始日期值。
在表格中查找数据的最大日起，若表格为空，则默认填补'2000-01-01'.
上述最大日期与指定日期'2023-05-01'的最大值则为开始日期
*/
SET @maxLoop = (SELECT DATEDIFF(DAY, @startDate, DATEADD(DAY, -@interval, GETDATE())));
PRINT @startDate
PRINT @maxLoop
/*
计算循环次数
*/
WHILE @maxLoop > 0
BEGIN

/*
begin try
	为发生错误时执行的语句
end try
begin catch
	发生错误时执行的语句
end catch
*/
BEGIN TRY

DELETE FROM dbo.BuriedWordListingVv WHERE 1=1;

/*
设置定义执行拼接的变量
附录：
1.SQL的转义符为'。拼接时，两个 ' 即 '' 表示为 '，四个 ' ,即 '''' 表示 '' 
2.数字类型的内容需要转成字符串类型
*/
SET @execSql ='
INSERT INTO dbo.BuriedWordListingVv
(
    OrderSourceID,
    ItemID,
    Date
)  SELECT * FROM OPENQUERY([LINK],'
SET @execSql=@execSql +    '    ''select
        ebq.OrderSourceId
        , ebq.ProductID
        , date_format(date_sub(now(), interval 18 day), "%Y-%m-%d")
from m_ebay_buriedwords_queue ebq
where ebq.date >= date_format(date_sub(now(), interval ' + STR(@maxLoop+@interval-1) + ' day), "%Y%m%d")+0
        and ebq.date < date_format(date_sub(now(), interval ' + STR(@maxLoop+@interval-2) + ' day), "%Y%m%d")+0
        and ebq.status=1
        and ebq.type=202
group by ebq.OrderSourceId
        , ebq.ProductID '' ) '
;
PRINT @execSql;
PRINT        CONVERT(DATETIME2, DATEADD(DAY, -(@maxLoop+@interval-1), GETDATE()),120);
EXEC(@execSql);
/*
拼接完成，打印拼接字符串，并执行。
*/
DROP TABLE IF EXISTS #tempRes;
WITH #tempBuried AS (
        SELECT
                COUNT(DISTINCT CASE WHEN ti.ItemID IS NOT NULL THEN ros.OrderCode ELSE NULL END) 订单数
                , SUM(CASE WHEN ti.ItemID IS NOT NULL THEN ros.ProductNum+ros.ProdutNumUpdate ELSE 0 END) 销量
                , COUNT(DISTINCT CASE WHEN ti.ItemID IS NOT NULL THEN ros.OrderItemId ELSE NULL END) 出单listing数
        FROM dbo.BuriedWordListingVv ti(NOLOCK)
        INNER JOIN srv_lnk_erpdb_readonly.irobotbox.dbo.RB_Report_OrderSettlement ros (NOLOCK) ON ros.CustomerID=1 AND ti.ItemID = ros.OrderItemId 
        INNER JOIN srv_lnk_erpdb_readonly.irobotbox.dbo.RB_OrderSource os (NOLOCK) ON os.CustomerID = ros.CustomerID AND os.id=ros.OrderSourceID
        WHERE ros.CustomerID=1
                AND ros.PayTime >= DATEADD(DAY, 0, ti.Date)
                AND ros.PayTime < DATEADD(DAY, 7, ti.Date)
                AND ros.OrderStatus=0
                AND os.OrderSourceType=2
                AND os.OrderSourceName LIKE '直发%'
) SELECT
        CONVERT(DATE,DATEADD(DAY, -(@maxLoop+@interval-1), GETDATE())) 日期 
        , pn.刊登listing数量
        , tb.出单listing数
        , tb.出单listing数 * 1.0 / pn.刊登listing数量 刊登listing7天出单率
    , tb.订单数
    , tb.销量
        INTO #tempRes
FROM #tempBuried tb, (SELECT COUNT(DISTINCT p.ItemID) 刊登listing数量 FROM dbo.BuriedWordListingVv p (NOLOCK)) pn
;
-- SELECT * FROM #tempRes

INSERT dbo.BuriedListing_ConversionRate
(
    BuriedDate,
    PublishListingQuantity,
    OrderListingQuantity,
    ConversionRate,
    OrderQuantity,
    SalesQauntity
) SELECT * FROM #tempRes

END TRY
BEGIN CATCH
        PRINT '--- 有问题 ---'
        PRINT        CONVERT(DATETIME2, DATEADD(DAY, -(@maxLoop+@interval-1), GETDATE()),120);
        PRINT '--- ------ ---'
END CATCH
/*
捕捉错误，打印情况，直接进入下一次循环
*/
SET @maxLoop = @maxLoop - 1;

END
/*
***************************************************
******************* 分 隔 区 域 *******************
***************************************************
*/