I am new to SQL Server stored procedures ... I have written a code which is not looping correctly I believe ...
Note: I am unable to use a Debugger so to debug I have used PRINT commands (old school style)
I am using SQL Server 2005
Code enters into the loop and PRINTs the lines only once - where else their are 29 Totals records to be looped and worked on
PRINT 'check if rows are their or not for Adjusting X flags records Results 5 '
SELECT
td_clientcd , td_scripcd, cm_name, td_scripnm,
sum(td_bqty) td_qty,
sum(td_sqty) td_sqty,
sum(td_bqty-td_sqty) net
FROM
##VX, Client_master with (nolock)
WHERE
td_clientcd = cm_cd
AND td_clientcd = @client_cd
GROUP BY
td_clientcd, cm_name, td_scripcd, td_scripnm
HAVING
sum(td_bqty - td_sqty) <> 0
ORDER BY
td_clientcd , td_scripcd
DECLARE dataX_Cursor CURSOR FOR
SELECT td_clientcd , td_scripcd,cm_name,td_scripnm, sum(td_bqty) td_bqty ,sum(td_sqty) td_sqty, sum(td_bqty-td_sqty) net FROM ##VX,Client_master with (nolock) where td_clientcd = cm_cd and td_clientcd = @client_cd group by td_clientcd,cm_name,td_scripcd,td_scripnm having sum(td_bqty - td_sqty) <> 0 ORDER BY td_clientcd , td_scripcd
OPEN dataX_Cursor
PRINT 'i am at 144'
DECLARE @tempSumQty INT -- has the qty of the Lower Side
DECLARE @tempHigherSideFlag CHAR -- show which is the Higher side Sell (S) or Buy (B)
FETCH NEXT FROM dataX_Cursor INTO @td_clientcode, @td_scripcode, @cm_name, @td_scripname, @td_buyqty, @td_sellqty, @net
WHILE @@FETCH_STATUS = 0
BEGIN
--Example
-- select * from ##VX where td_clientcd = '26555 ' and td_scripcd = '532804' and td_bsflag = 'B' and td_flag = 'N' order by td_dt desc,td_stlmnt desc
-- update ##VX set td_flag = 'X' where td_srno = 308
PRINT 'I am at 155'
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
if @td_buyqty > @td_sellqty
BEGIN
SET @tempSumQty = @td_sellqty -- Sets the sum of Lower side Qty
SET @tempHigherSideFlag = 'B'
END
ELSE
BEGIN
SET @tempSumQty = @td_buyqty -- Sets the sum of Lower side Qty
SET @tempHigherSideFlag = 'S'
END -- This will get the higher Side Qty
PRINT @tempSumQty
PRINT @tempHigherSideFlag
IF @td_buyqty = 0 OR @td_sellqty = 0 -- Just update the flag to X
BEGIN
-- select a single/multiple records and loop through it and see if
PRINT 'in OR Condition of X flags'
DECLARE record_Cursor CURSOR FOR
select * from ##VX where td_clientcd = @td_clientcode and td_scripcd = @td_scripcode and td_bsflag = @tempHigherSideFlag and td_flag = 'N' order by td_dt desc,td_stlmnt desc -- gets the records will needs to be marked X, which are from higher side.
OPEN record_Cursor
Fetch Next From record_Cursor Into @td_companycode ,@td_stlmnt,@td_clientcd , @td_scripcd, @td_dt, @td_srno, @td_bsflag,@td_bqty, @td_sqty, @td_rate, @td_marketrate,@td_flag,@td_scripnm,@td_desc
-- looping
While @@Fetch_Status = 0
BEGIN
-- update the old record with a flag of X
update ##VX set td_flag = 'X' where td_srno = @td_srno
-- fetch next
Fetch Next From record_Cursor Into @td_companycode ,@td_stlmnt,@td_clientcd , @td_scripcd, @td_dt, @td_srno, @td_bsflag,@td_bqty, @td_sqty, @td_rate, @td_marketrate,@td_flag,@td_scripnm,@td_desc
END
--Close record_Cursor
--Deallocate record_Cursor
END -- End of Fetch
-- if need to adjust the records with an insert and a update
IF @td_buyqty <> 0 AND @td_sellqty <> 0 -- Adjust the record with an insert and update the flag to X
BEGIN
-- select a single/multiple records and loop through it and see if
PRINT 'in AND Condition of X flags'
DECLARE record_Cursor CURSOR FOR
select * from ##VX where td_clientcd = @td_clientcode and td_scripcd = @td_scripcode and td_bsflag = @tempHigherSideFlag and td_flag = 'N' order by td_dt desc,td_stlmnt desc -- gets the records will needs to be marked X, which are from higher side.
OPEN record_Cursor
Fetch Next From record_Cursor Into @td_companycode ,@td_stlmnt,@td_clientcd , @td_scripcd, @td_dt, @td_srno, @td_bsflag,@td_bqty, @td_sqty, @td_rate, @td_marketrate,@td_flag,@td_scripnm,@td_desc
-- looping
While @@Fetch_Status = 0
BEGIN
DECLARE @CurrentRowQty INT
-- PRINT 'i am at 198 ' + CONVERT( VARCHAR(2), @tempHigherSideFlag )
IF @tempHigherSideFlag = 'S'
BEGIN
SET @CurrentRowQty = @td_sqty
END
ELSE IF @tempHigherSideFlag = 'B'
BEGIN
SET @CurrentRowQty = @td_bqty
END
IF @tempSumQty > @CurrentRowQty
BEGIN
SET @tempSumQty = @tempSumQty - @CurrentRowQty
END
ELSE
BEGIN
-- PRINT 'i am at 213 ' + CONVERT( VARCHAR(2), @tempHigherSideFlag )
IF @tempHigherSideFlag = 'S'
BEGIN
SET @td_sqty = @td_sqty - @tempSumQty
insert into ##VX select td_companycode ,td_stlmnt,td_clientcd , td_scripcd, td_dt, td_bsflag, td_bqty, @tempSumQty, td_rate, td_marketrate,td_flag,td_scripnm,'' from ##VX where td_srno = @td_srno
update ##VX set td_flag = 'X' ,td_sqty = @td_sqty where td_srno = @td_srno
END
ELSE -- IF @tempHigherSideFlag = 'B'
BEGIN
SET @td_bqty = @td_bqty - @tempSumQty
insert into ##VX select td_companycode ,td_stlmnt,td_clientcd , td_scripcd, td_dt, td_bsflag, @tempSumQty, td_sqty, td_rate, td_marketrate,td_flag,td_scripnm,'' from ##VX where td_srno = @td_srno
update ##VX set td_flag = 'X' ,td_bqty = @td_bqty where td_srno = @td_srno
END
END -- end of else
-- fetch next
Fetch Next From record_Cursor Into @td_companycode ,@td_stlmnt,@td_clientcd , @td_scripcd, @td_dt, @td_srno, @td_bsflag,@td_bqty, @td_sqty, @td_rate, @td_marketrate,@td_flag,@td_scripnm,@td_desc
END
--Close record_Cursor
--Deallocate record_Cursor
END
-- FETCH NEXT FROM dataX_Cursor INTO @td_clientcode, @td_scripcode, @cm_name, @td_scripname, @td_buyqty, @td_sellqty, @net
END -- End of Fetch
and finally I
Deallocate record_Cursor
Close dataX_Cursor
Deallocate dataX_Cursor
