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=@Creat _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 = @Creat _NUM)
BEGIN
Declare @ExistingInvoiceAmount float
select @ExistingInvoiceAmount = INVOICE_LINE_AMOUNT from ADDC_DOF_InvocieDetails where STATEMENT_ID = @STATEMENT_ID and ACCOUNT_NUM = @Creat _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 = @Creat _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 Moroz _ID,@Invoice _ID,@Invoice _LINE_ID,@Creat _NUM,@Creat _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