question

venkatasivayadav-2961 avatar image
0 Votes"
venkatasivayadav-2961 asked ·

not able to generate the record using storeprocedure

HI ,

I am using store procedure to insert and update my table in SQL ,
but once i generate the schema using store procedure i am not able to see the record but fields are under root node .due to this not able to insert multiple records in SQL , please help me see below my store procedure .

USE [ADDC_DOF_Integration]
GO

/ Object: StoredProcedure [dbo].[uspInsertorUpdateInvoice] Script Date: 6/18/2021 11:43:41 PM /
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




  • =============================================

  • Author: <Author,,Name>

  • Create date: <Create Date,,>

  • Description: <Description,,>

  • =============================================
    ALTER PROCEDURE [dbo].[uspInsertorUpdateInvoice]
    -- Add the parameters for the stored procedure here

    @CONSTRUCT_ID nvarchar(50) NULL,
    @STATEMENT_ID nvarchar(50) NULL,
    @STATEMENT_NAME nvarchar(500) NULL,
    @ORG_ID nvarchar(10) NULL,
    @BILL_ID nvarchar(50) NULL,
    @INVOICE_ID nvarchar(50) NULL,
    @INVOICE_LINE_ID nvarchar(50) NULL,
    @ACCOUNT_NUM nvarchar(50) NULL,
    @ACCOUNT_NAME nvarchar(500) NULL,
    @INVOICE_DATE nvarchar(20) NULL,
    @INVOICE_LINE_AMOUNT nvarchar(20) NULL,
    @INVOICE_DESCRIPTION nvarchar(500) NULL,
    @INVOICE_PAYMENT_STATUS nvarchar(20) NULL,
    @INVOICE_PAYMENT_DATE nvarchar(20) NULL,
    @INVOICE_CHECK_NUM nvarchar(50) NULL,
    @INVOICE_VOUCHER_NUM nvarchar(50) NULL,
    @StatementType nvarchar(20) = ''

AS
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;





 if @StatementType = 'UPDATE'
 BEGIN
 UPDATE [dbo].[ADDC_DOF_InvocieDetails]

SET INVOICE_PAYMENT_STATUS = @INVOICE_PAYMENT_STATUS, INVOICE_PAYMENT_DATE = @INVOICE_PAYMENT_DATE, INVOICE_CHECK_NUM=@INVOICE_CHECK_NUM,INVOICE_DESCRIPTION =@INVOICE_DESCRIPTION,
INVOICE_VOUCHER_NUM=@INVOICE_VOUCHER_NUM,UPDATED_DATE = getdate() WHERE STATEMENT_ID=@STATEMENT_ID and ACCOUNT_NUM=@ACCOUNT_NUM

END

if @StatementType = 'INSERT'

BEGIN
if exists (select STATEMENT_ID,ACCOUNT_NUM From ADDC_DOF_InvocieDetails where STATEMENT_ID = @STATEMENT_ID and ACCOUNT_NUM = @ACCOUNT_NUM)
BEGIN
Declare @ExistingInvoiceAmount float
select @ExistingInvoiceAmount = INVOICE_LINE_AMOUNT from ADDC_DOF_InvocieDetails where STATEMENT_ID = @STATEMENT_ID and ACCOUNT_NUM = @ACCOUNT_NUM

Update [dbo].[ADDC_DOF_InvocieDetails]

SET INVOICE_LINE_AMOUNT = (@ExistingInvoiceAmount+@INVOICE_LINE_AMOUNT), UPDATED_DATE = getdate() where STATEMENT_ID = @STATEMENT_ID and ACCOUNT_NUM = @ACCOUNT_NUM

END
else
BEGIN
INSERT into ADDC_DOF_InvocieDetails (CONSTRUCT_ID,STATEMENT_ID,STATEMENT_NAME,ORG_ID,BILL_ID,INVOICE_ID,INVOICE_LINE_ID,ACCOUNT_NUM,ACCOUNT_NAME,INVOICE_DATE,INVOICE_LINE_AMOUNT,INVOICE_DESCRIPTION,INVOICE_PAYMENT_STATUS,INVOICE_PAYMENT_DATE,INVOICE_CHECK_NUM,INVOICE_VOUCHER_NUM,CREATED_DATE)
values(@CONSTRUCT_ID,@STATEMENT_ID,@STATEMENT_NAME,@ORG_ID,@BILL_ID,@INVOICE_ID,@INVOICE_LINE_ID,@ACCOUNT_NUM,@ACCOUNT_NAME,@INVOICE_DATE,@INVOICE_LINE_AMOUNT,@INVOICE_DESCRIPTION,@INVOICE_PAYMENT_STATUS,@INVOICE_PAYMENT_DATE,@INVOICE_CHECK_NUM,@INVOICE_VOUCHER_NUM,GETDATE())

END
END
END

GO


107232-image.png
107224-untitled.png


biztalk-server-all
image.png (14.8 KiB)
untitled.png (17.4 KiB)
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers