时间戳

首页 > 解决方案 > SQL Server：存储过程不添加任何数据而没有任何错误

sql - SQL Server：存储过程不添加任何数据而没有任何错误

问题描述

我有两个表主和详细信息。我将 xml 作为参数值传递给存储过程，但是当我的存储过程代码执行时，没有插入任何数据，也没有引发错误。我无法理解问题出在哪里。

谁能帮我找出导致问题的存储过程代码中的错误区域？

表结构：

CREATE TABLE TblTickerTemplate 
(
    ID INT IDENTITY,
    Ticker VARCHAR(20),
    LineItem VARCHAR(100),
    Insertdate DATETIME DEFAULT GETDATE()

    CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID)
);

ALTER Table TblTickerTemplate
    ADD CONSTRAINT UC_Ticker_LineItem UNIQUE (Ticker, LineItem)

CREATE TABLE TblTickerTemplateDetail 
(
    MasterTemplateID INT,
    ID INT IDENTITY,
    LineItem VARCHAR(100),
    XFundCode VARCHAR(100),
    Action CHAR(1),
    UserID VARCHAR(20),
    Insertdate DATETIME DEFAULT GETDATE()

    FOREIGN KEY(MasterTemplateID) REFERENCES TblTickerTemplate(ID)
);

程序代码：

ALTER PROCEDURE USP_InsertUniqueLineItems
    (@INPUTXML xml,
     @STATUS VARCHAR(MAX) OUTPUT)
AS 
BEGIN
    SET NOCOUNT ON

    BEGIN TRANSACTION   
    BEGIN TRY  
        DECLARE @TblTickerTemplateDetailID AS INT 
        DECLARE @XMLFormat AS INT
        DECLARE @Ticker AS VARCHAR(MAX)
        DECLARE @ID AS INT
        DECLARE @Lineitem AS VARCHAR(MAX)
        DECLARE @XFundCode AS VARCHAR(MAX)
        DECLARE @UserID AS VARCHAR(MAX)

        --load xml data into cursor
        EXEC sp_xml_preparedocument @XMLFormat OUTPUT, @INPUTXML

        DECLARE CURRECORD CURSOR LOCAL FOR
            SELECT Ticker, ID, Lineitem, XFundCode, @UserID
            FROM OPENXML (@XMLFORMAT, '/Lineitems', 1)
                WITH (
                    Ticker    VARCHAR(MAX),
                    ID        INT,
                    Lineitem  VARCHAR(MAX),
                    XFundCode VARCHAR(MAX),
                    UserID    VARCHAR(MAX)
                 )


        SET @TblTickerTemplateDetailID = 0

        -- open cursor
        OPEN CURRECORD
        FETCH NEXT FROM CURRECORD INTO @Ticker,@ID,@Lineitem,@XFundCode,@UserID

        -- iterate in cursor to fetch value
        WHILE (@@FETCH_STATUS=0)
        BEGIN

            -- if ID == 0 then new data it will be inserted in TblTickerTemplate & TblTickerTemplateDetail table
            IF @ID = 0
            BEGIN
                IF NOT EXISTS(SELECT LineItem  FROM TblTickerTemplate WHERE LineItem = @Lineitem AND Ticker = @Ticker)
                BEGIN
                    INSERT INTO TblTickerTemplate(Ticker,LineItem)
                        VALUES (@Ticker,@Lineitem)

                    SET @TblTickerTemplateDetailID = SCOPE_IDENTITY()

                    INSERT INTO TblTickerTemplateDetail (MasterTemplateID,LineItem,XFundCode,UserID,[Action])
                        VALUES (@TblTickerTemplateDetailID,@Lineitem,@XFundCode,@UserID,'I')                    
                END
            END
            ELSE
            BEGIN
                    -- if ID > 0 means existing data & the data will be inserted in TblTickerTemplateDetail table only

                    INSERT INTO TblTickerTemplateDetail (MasterTemplateID,LineItem,XFundCode,UserID,[Action])
                        VALUES (@ID,@Lineitem,@XFundCode,@UserID,'U')
            END

        FETCH NEXT FROM CURRECORD INTO @Ticker,@ID,@Lineitem,@XFundCode,@UserID
        END

        IF @ID = 0
        BEGIN                       
            SET @STATUS='New Line items successfully inserted' 
        END
        ELSE IF @ID > 0
        BEGIN     
            SET @STATUS='Existing Line items successfully updated' 
        END

        CLOSE CURRECORD
        DEALLOCATE CURRECORD    
        COMMIT TRANSACTION



    END TRY  

    BEGIN CATCH  

        DECLARE @ERROR INT, @MESSAGE VARCHAR(4000)
        SELECT @ERROR = ERROR_NUMBER(),@MESSAGE = ERROR_MESSAGE()   

        --SET @STATUS='Line items insert fail' 
        ROLLBACK TRANSACTION 

        RAISERROR ('USP_InsertUniqueLineItems:', 16, 1, @ERROR, @MESSAGE) 
        --SET @STATUS='Fail-- '+@MESSAGE
        --RETURN

    END CATCH;

    SET NOCOUNT OFF
END

Calling procedure this way
--------------------------

Declare @inputxml xml  
Declare @Status VARCHAR(MAX)

 set @inputxml='<Lineitems>
  <Lineitem>
    <Ticker>TER</Ticker>
    <ID>0</ID>
    <LineItem>Net Revenue</LineItem>
    <XFundCode>TRIN</XFundCode>
    <UserID>TDP</UserID>
  </Lineitem>
  <Lineitem>
    <Ticker>TER</Ticker>
    <ID>0</ID>
    <LineItem>Cost of Revenue</LineItem>
    <XFundCode>XXP</XFundCode>
    <UserID>TDP</UserID>    
  </Lineitem>
</Lineitems>'

 EXEC [dbo].USP_InsertUniqueLineItems @inputxml, @Status output

 Select @Status

编辑

我这样解决了

/*
SAMPLE XML to insert
<Lineitems>
  <Lineitem>
    <Ticker>TER</Ticker>
    <ID>0</ID>
    <LineItem>Net Revenue</LineItem>
    <XFundCode>TRIN</XFundCode>
    <UserID>TDP</UserID>
  </Lineitem>
  <Lineitem>
    <Ticker>TER</Ticker>
    <ID>0</ID>
    <LineItem>Cost of Revenue</LineItem>
    <XFundCode>XXP</XFundCode>
    <UserID>TDP</UserID>    
  </Lineitem>
</Lineitems>
*/

ALTER Procedure USP_InsertUniqueLineItems
(
    @INPUTXML xml,
    @STATUS VARCHAR(MAX) OUTPUT  
)

AS 

BEGIN
    SET NOCOUNT ON

    BEGIN TRANSACTION   
    BEGIN TRY  
        Declare @TblTickerTemplateDetailID  AS INT 

        Declare @XMLFormat                  AS INT

        Declare @Ticker                     AS VARCHAR(MAX)
        Declare @ID                         AS INT
        Declare @Lineitem                   AS VARCHAR(MAX)
        Declare @XFundCode                  AS VARCHAR(MAX)
        Declare @UserID                     AS VARCHAR(MAX)

        --load xml data into cursor
        Exec sp_xml_preparedocument @XMLFormat OUTPUT, @INPUTXML

        DECLARE CURRECORD
        CURSOR LOCAL FOR
        SELECT ID,Ticker,LineItem,XFundCode,UserID
        FROM OPENXML (@XMLFORMAT, '/Lineitems/Lineitem', 2)
        WITH (
            ID                      INT,
            Ticker                  VARCHAR(MAX),
            LineItem                VARCHAR(MAX),
            XFundCode               VARCHAR(MAX),
            UserID                  VARCHAR(MAX)
         )


        SET @TblTickerTemplateDetailID = 0

        -- open cursor
        OPEN CURRECORD
        FETCH NEXT FROM CURRECORD INTO @ID,@Ticker,@Lineitem,@XFundCode,@UserID

        -- iterate in cursor to fetch value
        WHILE (@@FETCH_STATUS=0)
        BEGIN

            -- if ID == 0 then new data it will be inserted in TblTickerTemplate & TblTickerTemplateDetail table
            IF @ID = 0
            BEGIN
                IF NOT EXISTS(SELECT *  FROM TblTickerTemplate WHERE LineItem = @Lineitem AND Ticker = @Ticker)
                BEGIN
                    Print @Ticker+' '+@Lineitem

                    INSERT INTO TblTickerTemplate(Ticker,LineItem)
                        VALUES (@Ticker,@Lineitem)

                    SET @TblTickerTemplateDetailID = SCOPE_IDENTITY()
                    SET @STATUS='New Line items successfully inserted' 

                    INSERT INTO TblTickerTemplateDetail (MasterTemplateID,LineItem,XFundCode,UserID,[Action])
                        VALUES (@TblTickerTemplateDetailID,@Lineitem,@XFundCode,@UserID,'I')                    
                END
            END
            ELSE
            BEGIN
                    -- if ID > 0 means existing data & the data will be inserted in TblTickerTemplateDetail table only

                    INSERT INTO TblTickerTemplateDetail (MasterTemplateID,LineItem,XFundCode,UserID,[Action])
                        VALUES (@ID,@Lineitem,@XFundCode,@UserID,'U')
            END

        FETCH NEXT FROM CURRECORD INTO @ID,@Ticker,@Lineitem,@XFundCode,@UserID
        END

        -- storing message to OUTPUT type variable
        IF @ID = 0
        BEGIN                       
            SET @STATUS='New Line items successfully inserted' 
        END
        ELSE IF @ID > 0
        BEGIN     
            SET @STATUS='Existing Line items successfully updated' 
        END

        CLOSE CURRECORD
        DEALLOCATE CURRECORD    
        COMMIT TRANSACTION

    END TRY  

    BEGIN CATCH  

        -- Error handling part
        DECLARE @ERROR INT, @MESSAGE VARCHAR(4000)
        SELECT @ERROR = ERROR_NUMBER(),@MESSAGE = ERROR_MESSAGE()   

        SET @STATUS='Fail-- '+@MESSAGE      
        ROLLBACK TRANSACTION 

        --RAISERROR ('USP_InsertUniqueLineItems:', 16, 1, @ERROR, @MESSAGE) 
        RETURN

    END CATCH;

    SET NOCOUNT OFF
END

但如果我取消注释这一行

RAISERROR ('USP_InsertUniqueLineItems:', 16, 1, @ERROR, @MESSAGE)

然后抛出错误。如何解决？分享一些想法。谢谢

标签： sqlsql-server

解决方案

嗨，在插入时尝试此代码。

Declare @inputxml xml  


 set @inputxml='<Lineitems>
  <Lineitem>
    <Ticker>TER</Ticker>
    <ID>1</ID>
    <LineItem>Net Revenue</LineItem>
    <XFundCode>TRIN</XFundCode>
    <UserID>TDP</UserID>
  </Lineitem>
  <Lineitem>
    <Ticker>TER</Ticker>
    <ID>2</ID>
    <LineItem>Cost of Revenue</LineItem>
    <XFundCode>XXP</XFundCode>
    <UserID>TDP</UserID>    
  </Lineitem>
</Lineitems>'


        SELECT

      T.C.value('(ID)[1]', 'int')

      ,T.C.value('(Ticker)[1]', 'varchar(max)')

      ,T.C.value('(LineItem)[1]', 'varchar(max)')

      ,T.C.value('(XFundCode)[1]', 'varchar(max)')
      ,T.C.value('(UserID)[1]', 'varchar(max)')


   FROM @inputxml.nodes('Lineitems/Lineitem') T(C)

请根据您在 select 语句中的要求更改变量数据类型，否则您会出错。

推荐阅读