[實作筆記]SQL Server 與 Linked Server 注意事項

原始 SQL

1
2
3
4
5
6
7
8
9
10
11
USE NationalDB
GO
BEGIN TRY
---Select Cross Linked Server
Select Name From LinkedServer.Shop.dbo.Member
---DO SOMETHING
END TRY
BEGIN CATCH
DECLARE @errorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
RAISERROR (@errorMessage, 16, 1);
END CATCH
  1. 變數先行宣告
  2. 非必要不要宣告MAX
  3. 跨DB存取不使用四節式的查詢語法,改用 sp_executesql

修改後的語法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE NationalDB
GO
DECLARE @errorMessage NVARCHAR(4000) = ERROR_MESSAGE();
BEGIN TRY
---Select Cross Linked Server
EXEC LinkedServer.Shop.dbo.sp_executesql N'
SELECT @Name = Name
FROM dbo.Member
WHERE MemberId = @MemberId
AND Valid = 1',N'@MemberId bigint,@Name varchar(20) OUTPUT',@MemberId,@Name OUTPUT
---DO SOMETHING
END TRY
BEGIN CATCH
RAISERROR (@errorMessage, 16, 1);
END CATCH

(fin)

Please enable JavaScript to view the LikeCoin.