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

