sqlserver跨库操作数据

垮库只能读操作，写操作需要设置权限。

+ View Code?

USE [jdddb]

GO

/****** Object:  StoredProcedure [dbo].[proc_LYOrderCancel]    Script Date: 02/07/2017 11:18:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:      赵伟

-- Create date: 20170122

-- Description: 疗养宝订单取消

-- =============================================

--EXEC proc_LYOrderCancel '15201701201037185748','xf0057',17,1,37125,141129,'2017-01-20',1,0

ALTER PROCEDURE [dbo].[proc_LYOrderCancel]

    (

        @orderSn VARCHAR(100),

        @userName NVARCHAR(100), --用户名称

        @orderType INT, --订单类型

        @status INT, --订单当前状态

        @jdid INT, --酒店ID

        @fjlxid INT, --房间ID

        @idate DATETIME, --预订时间

        @num INT ,  --入住天数，用于减房态

        @flag INT OUTPUT --返回标识

    )

AS

BEGIN

    SET NOCOUNT ON;

    BEGIN TRY

        IF EXISTS(SELECT srvname FROM MASTER.DBO.SYSSERVERS WHERE SrvName='172.18.105.63')

        BEGIN

            EXEC SP_DROPSERVER '172.18.105.63','DropLogins'

        END

        EXEC sp_addlinkedserver '172.18.105.63',N'SQL Server'

        EXEC sp_addlinkedsrvlogin '172.18.105.63', 'false', NULL, 'test', 'xinfuka'

         

        BEGIN TRANSACTION

 

        IF EXISTS(SELECT Id FROM [172.18.105.63].[Xinfu0816].[dbo].[Orders] WHERE OrderType=@orderType AND OrderSn=@orderSn AND UserName=@userName)

        BEGIN

            UPDATE [172.18.105.63].[Xinfu0816].[dbo].[Orders] SET [Status]=4 WHERE  OrderType=@orderType AND OrderSn=@orderSn AND UserName=@userName

        END

         

        IF EXISTS(SELECT 1 FROM dbo.JD_Orders WHERE orderType=@orderType AND OrderSn=@orderSn AND ydr=@userName)

        BEGIN

            UPDATE dbo.JD_Orders SET zt=4 WHERE  orderType=@orderType AND OrderSn=@orderSn AND ydr=@userName

        END

         

        IF(@status!=2)--待支付的不需要减房态

        BEGIN

 

            DECLARE @col VARCHAR(100);

            SET @col ='sl'+ CONVERT(varchar(100),DATEPART(DAY,@idate));

            IF EXISTS(SELECT id FROM dbo.JD_FT WHERE jdid=@jdid AND fjlxid=@fjlxid AND iyear=DATEPART(YEAR,@idate) AND imonth=DATEPART(MONTH,@idate))

            BEGIN

            DECLARE @sql VARCHAR(1000);

            SET @sql='UPDATE dbo.JD_FT SET '+@col+'='+@col+'-'+CONVERT(VARCHAR(10),@num)+' WHERE jdid='+CONVERT(VARCHAR(10),@jdid)+' AND fjlxid='+CONVERT(VARCHAR(10),@fjlxid)+' AND iyear='+CONVERT(VARCHAR(10),DATEPART(YEAR,@idate))+' AND imonth='+CONVERT(VARCHAR(10),DATEPART(MONTH,@idate))+''

            PRINT @sql;

            EXEC(@sql);

            --UPDATE dbo.JD_FT SET @col=@col-@num

            --WHERE jdid=@jdid AND fjlxid=@fjlxid AND iyear=DATEPART(YEAR,@idate) AND imonth=DATEPART(MONTH,@idate)

            END

        END

 

        SET @flag=1;

        COMMIT TRANSACTION

    END TRY

    BEGIN CATCH

        SET @flag=-1;

        ROLLBACK TRANSACTION

        /*定义要返回的异常信息变量*/

        DECLARE @ErrorMessage NVARCHAR(4000);

        DECLARE @ErrorSeverity INT;

        DECLARE @ErrorState INT;

        /*收集可能出现的异常信息*/

        SELECT

        @ErrorMessage = ERROR_MESSAGE(),

        @ErrorSeverity = ERROR_SEVERITY(),

        @ErrorState = ERROR_STATE();

        /*抛出异常*/

        RAISERROR (@ErrorMessage,  -- Message text.

               @ErrorSeverity, -- Severity.

               @ErrorState     -- State.

               );

    END CATCH 

END