I have created a stored procedure for bulk invoice generation. On execution of the stored procedure, the values are successfully returned for all the columns except FromDate and ToDate. For these 2 columns, NULL values are displaying.
This here is the procedure that I have created. Please let me know what I am doing wrong here?
SET @fromDt = CONVERT(VARCHAR, DATEADD(d, -(DAY(DATEADD(m, -1, @currentdate - 2))), DATEADD(m, -1, @currentdate - 1)), 106)
ALTER PROCEDURE Bulkinvoicegeneration_lko
@InvoiceMonth VARCHAR(20),
@PrjId BIGINT,
@CustomerId NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @fromDt DATETIME,
@monthdays INT,
@ToDate DATETIME,
@Currentdate DATETIME,
@Year INT
SET @currentdate = CONVERT(VARCHAR, @Year) + '-'
+ CONVERT(VARCHAR, @InvoiceMonth) + '-11'
SET @fromDt = CONVERT(VARCHAR, DATEADD(d, - (DAY(DATEADD(m, -1, @currentdate - 2))), DATEADD(m, -1, @currentdate - 1)), 106)
SET @ToDate = CONVERT(DATE, Dateadd(d, -(Day(@currentdate)), @currentdate))
SET @monthdays = DATEDIFF(d, @fromDt, @ToDate) + 1
SELECT *
INTO #consumerdata1
FROM
(SELECT
id, prjid, customerid, ucccatid, area, rate,
billingamount AS TotalAmt,
billingamount AS SubTotalAmt
FROM
consumermst_lko
WHERE
1 = 2) AS aa
BEGIN
INSERT INTO #consumerdata1
SELECT
prjid, customerid, ucccatid, area, rate,
billingamount AS TotalAmt,
billingamount AS SubTotalAmt
FROM
consumermst_lko
END
ALTER TABLE #consumerdata1
ADD invoiceno VARCHAR(20), invoicedate DATE, duedate DATE,
balanceamt NUMERIC(10, 2), roundoff NUMERIC(10, 2),
entrydate DATE, fromdate DATE, todate DATE,
userid INT, deleteflag INT, responsecode INT,
responsemessage VARCHAR(500), invoiceno_response VARCHAR(500)
UPDATE #consumerdata1
SET entrydate = Getdate()
UPDATE #consumerdata1
SET userid = 2
UPDATE #consumerdata1
SET balanceamt = 20
--update #CONSUMERDATA set billingdays = 30
UPDATE #consumerdata1
SET invoiceno = NULL,
invoicedate = CONVERT(DATE, GETDATE() - 1),
duedate = DATEADD(d, 15, CONVERT(DATE, GETDATE())),
roundoff = 0,
deleteflag = 0
DECLARE @id BIGINT,
@InvoiceNo NVARCHAR(150),
@InvoiceDate DATE,
@DueDate DATE,
@UccCatId BIGINT,
@rate NUMERIC(10, 2),
@Area NUMERIC(10, 2),
@TotalAmt NUMERIC(10, 2),
@BalanceAmt NUMERIC(10, 2),
@SubTotalAmt NUMERIC(10, 2),
@RoundOff NUMERIC(10, 2),
@EntryDate DATE,
@FromDate DATE,
@UserId BIGINT,
@deleteflag INT,
@RESPONSECODE INT,
@RESPONSEMESSAGE VARCHAR(255),
@INVOICENO_RESPONSE VARCHAR(20)
SET @prjid = 2
DECLARE consmr_cursor1 CURSOR FOR
SELECT
id, prjid, customerid, ucccatid, area, rate,
totalamt, subtotalamt, invoiceno, invoicedate, duedate,
balanceamt, roundoff, entrydate, fromdate, todate,
userid, deleteflag, responsecode, responsemessage,
invoiceno_response
FROM
#consumerdata1
OPEN consmr_cursor1
FETCH NEXT FROM consmr_cursor1
INTO @Id, @PrjId, @CustomerId, @UccCatId, @Area, @rate, @TotalAmt, @SubTotalAmt, @InvoiceNo, @InvoiceDate, @DueDate,
@BalanceAmt, @RoundOff, @EntryDate, @FromDate, @ToDate, @UserId, @deleteflag,
@ResponseCode, @ResponseMessage, @INVOICENO_RESPONSE
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [dbo].[Demoinvoice]
@Id = @Id,
@InvoiceNo =@InvoiceNo,
@PrjId = @PrjId,
@CustomerId = @CustomerId,
@InvoiceDate = @InvoiceDate,
@InvoiceMonth = @InvoiceMonth,
@DueDate = @DueDate,
@UccCatId = @UccCatId,
@Rate = @Rate,
@Quantity = @Area,
@TotalAmt = @TotalAmt,
@BalanceAMT = @BalanceAmt,
@SubTotalAmt = @SubtotalAmt,
@RoundOff = @RoundOff,
@EntryDate = @EntryDate,
@FromDate = @FromDate,
@ToDate = @ToDate,
@UserId = @UserId,
@deleteflag = @deleteflag,
@RESPONSECODE = @RESPONSECODE,
@RESPONSEMESSAGE = @RESPONSEMESSAGE output,
@INVOICENO_RESPONSE = @INVOICENO_RESPONSE output
UPDATE #consumerdata1
SET responsecode = @RESPONSECODE,
responsemessage = @RESPONSEMESSAGE,
invoiceno_response = @INVOICENO_RESPONSE
WHERE customerid = @CustomerId
FETCH NEXT FROM consmr_cursor1
INTO @Id, @PrjId, @CustomerId, @UccCatId, @Area, @rate, @TotalAmt, @SubTotalAmt, @InvoiceNo, @InvoiceDate,
@DueDate, @BalanceAmt, @RoundOff, @EntryDate, @FromDate, @ToDate, @UserId, @deleteflag,
@ResponseCode, @ResponseMessage, @INVOICENO_RESPONSE
END
CLOSE consmr_cursor1
DEALLOCATE consmr_cursor1
SELECT *
FROM #consumerdata1
-- WHERE @RESPONSECODE <> 200
END
EXECUTE Bulkinvoicegeneration_lko
1,
2,
'LKO00066801'
Check this section in your procedure
DECLARE @fromDt DATETIME,
@monthdays INT = 10,
@ToDate DATETIME,
@Currentdate DATETIME,
@Year INT , ---- you need to pass some value over there
@InvoiceMonth varchar(50) = '1' --- this is passed as parameter from your procedure
SET @currentdate = CONVERT(VARCHAR, @Year) + '-'
+ CONVERT(VARCHAR, @InvoiceMonth) + '-11'
Select @currentdate ---- this value is NULL, since @Year is not passed.
Since you are using @currentdate for generating your @frmdt and @todt so they are also NULL.
DECLARE @fromDt DATETIME,
@monthdays INT = 10,
@ToDate DATETIME,
@Currentdate DATETIME,
@Year INT = 2019,
@InvoiceMonth varchar(50) = '1' --- this is passed as parameter from your procedure
SET @currentdate = CONVERT(VARCHAR, @Year) + '-'
+ CONVERT(VARCHAR, @InvoiceMonth) + '-11'
Select @currentdate ---- this value is '2019-01-11 00:00:00.000'.
---- After that
SET @fromDt = CONVERT(VARCHAR, DATEADD(d, - (DAY(DATEADD(m, -1, @currentdate - 2))), DATEADD(m, -1, @currentdate - 1)), 106)
SET @ToDate = CONVERT(DATE, Dateadd(d, -(Day(@currentdate)), @currentdate))
SET @monthdays = DATEDIFF(d, @fromDt, @ToDate) + 1
RESULT
frmdt toDate Monthdays
2018-12-01 00:00:00.000 2018-12-31 00:00:00.000 31
Update your code for setting this @currentdate once it is done hope rest of the block will fit on right place for you.... ;)