if i uncomment this line RAISERROR ('USP_InsertUniqueLineItems:', 16, 1, @ERROR, @MESSAGE) then error is coming. what is wrong in my RAISERROR code ?
how to fix it...share some idea. thanks
my code
----------
/*
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
