Dunk

USE foodb
GO

-- 原始的SQL
-- 在測試環境跑可以正式環境有問題
-- 原因:正式環境的相容性層級為SQL 7.0，不支援collate語法 
DECLARE @usrid char(6)
SET @usrid = '123456';

SELECT ROL_ID COLLATE Chinese_Taiwan_Stroke_BIN AS [ROL_ID]
FROM
	fooSC..SC_ROL_USR
WHERE
	USR_ID = @usrid
	AND ROL_ID LIKE '林大貓%'
UNION
SELECT ROL_ID
FROM
	ROLUSR
WHERE
	USR_ID = @usrid
	
-- SQL 7.0寫法
-- 1. 建立table變數
-- 2. 將要查詢的欄位寫入table變數
-- 3. 將table變數與另一個目的表格做運算
DECLARE @usrid char(6)
SET @usrid = '123456';

declare @SC_ROL_USR table
(
 ROL_ID  nvarchar(20)
)

INSERT @SC_ROL_USR
SELECT cast(ROL_ID AS NVARCHAR(20)) AS [ROL_ID]
FROM
	fooSC..SC_ROL_USR
WHERE
	USR_ID = @usrid
	AND ROL_ID LIKE '林大貓%';

SELECT ROL_ID
FROM
	@SC_ROL_USR
UNION
SELECT ROL_ID
FROM
	ROLUSR
WHERE
	USR_ID = @usrid;