我正在嘗試根據 SQL Server 2016 中的條件結果填充臨時表。
無論哪種方式，臨時表都將具有相同的結構，但將根據條件使用不同的查詢進行填充。
下面的簡化示例腳本在 ELSE 塊 INSERT INTO 的語法檢查中失敗，錯誤為：
資料庫中已經有一個名為“#oneyrcon”的物件。
代碼：
IF OBJECT_ID('TEMPDB..#ONEYRCON') IS NOT NULL
DROP TABLE #ONEYRCON
IF @name IN ('asd', 'TVS')
SELECT tre.Dealermasterzone, tre.Dealermasterstate
INTO #ONEYRCON
FROM [dbo].[T_RE_POLICY_TRANSACTION] tre
INNER JOIN #ONEYRBASEEXPIRY ony ON tre.ChassisNo = ony.chassisno
WHERE (CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayCon
AND BusinessType = @Name
AND (ony.PolExpDateSub <= CAST(tre.InsPolicyCreatedDate AS date)))
OR (@lastdayC < @lastdayM --and @lastdayc <> @LastDayCon
AND CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayC
AND BusinessType = @Name)
GROUP BY
tre.Dealermasterzone, tre.Dealermasterstate
ELSE
SELECT tre.Dealermasterzone, tre.Dealermasterstate
INTO #ONEYRCON
FROM [dbo].[T_RE_POLICY_TRANSACTION] tre
INNER JOIN #ONEYRBASEEXPIRY ony ON tre.ChassisNo = ony.chassisno
AND tre.currentyearIcName = ony.CurrentYearICName
WHERE
(CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayCon
AND BusinessType = @Name
AND (ony.PolExpDateSub <= CAST(tre.InsPolicyCreatedDate AS date)))
OR (@lastdayC < @lastdayM --and @lastdayc <> @LastDayCon
AND CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayC
AND BusinessType = @Name)
GROUP BY
tre.Dealermasterzone,tre.Dealermasterstate
這是唯一的選擇嗎？或者有什么方法可以使這項作業？
uj5u.com熱心網友回復：
在 if 之前創建空的臨時表
IF OBJECT_ID('TEMPDB..#ONEYRCON') IS NOT NULL
DROP TABLE #ONEYRCON
SELECT tre.Dealermasterzone, tre.Dealermasterstate INTO #ONEYRCON
FROM [dbo].[T_RE_POLICY_TRANSACTION] tre
WHERE 1 = 0
IF @name IN ('asd', 'TVS')
INSERT #ONEYRCON SELECT tre.Dealermasterzone, tre.Dealermasterstate FROM
[dbo].[T_RE_POLICY_TRANSACTION] tre ...
ELSE
INSERT #ONEYRCON SELECT tre.Dealermasterzone, tre.Dealermasterstate FROM
[dbo].[T_RE_POLICY_TRANSACTION] tre ....
uj5u.com熱心網友回復：
只是一個想法。
僅使用 1 個查詢，但在連接中有一個額外的條件。
INNER JOIN #ONEYRBASEEXPIRY ony
ON tre.ChassisNo = ony.chassisno
AND (@name IN ('asd','TVS') OR tre.currentyearIcName = ony.CurrentYearICName)
或者，如果您DROP TABLE同時在IF和ELSE.
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/369119.html
標籤：sql-server 查询语句