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
變數先行宣告
非必要不要宣告MAX
跨 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