November 16, 2011 at 8:34 am
DECLARE mySQL_cur CURSOR FOR
SELECTaccno, accname
FROMtb1 T1 (Nolock)
LEFT JOINtb2 T2 (Nolock) ON T1.accno=T2.custcode
WHERET1.date=@date and
GROUP BYaccno, accname
OPEN mySQL_cur FETCH NEXT FROM mySQL_cur INTO @accno, @accname
WHILE @@FETCH_STATUS=0
BEGIN
SET @nettotal = @total1 - @total2
INSERT INTO tmpTb3
(accno, accname, total1, total2, nettotal)
VALUES(@accno, @accname, @total1, @total2, @nettotal)
INSERT INTOtmpTb3
(accno, accname, total1, total2, nettotal, value, detail)
SELECTaccno=@accno, accname=@accname, total1=@total1, total2=@total2, nettotal=@nettotal, value=@value, detail=@detail
FROMtb1 Tb1 (Nolock)
LEFT JOINtb2 Tb2 (Nolock) ON T1.accno=T2.custcode
WHERET1.date=@date
GROUP BYaccno
ORDER BYfcode
FETCH NEXT FROM mySQL_cur INTO @accno, @accname
END
CLOSE mySQL_cur
DEALLOCATE mySQL_cur
Above is currently command, but I would like to check @aaa parameter value to add command (See The underline line)
DECLARE mySQL_cur CURSOR FOR
SELECTaccno, accname
FROMtb1 T1 (Nolock)
LEFT JOINtb2 T2 (Nolock) ON T1.accno=T2.custcode
WHERET1.date=@date and
GROUP BYaccno, accname
OPEN mySQL_cur FETCH NEXT FROM mySQL_cur INTO @accno, @accname
WHILE @@FETCH_STATUS=0
BEGIN
SET @nettotal = @total1 - @total2
INSERT INTO tmpTb3
(accno, accname, total1, total2, nettotal)
VALUES(@accno, @accname, @total1, @total2, @nettotal)
INSERT INTOtmpTb3
(accno, accname, total1, total2, nettotal, value, detail)
SELECTaccno=@accno, accname=@accname, total1=@total1, total2=@total2, nettotal=@nettotal, value=@value, detail=@detail
FROMtb1 Tb1 (Nolock)
LEFT JOINtb2 Tb2 (Nolock) ON T1.accno=T2.custcode
WHERET1.date=@date
IF @aaa <> ''
AND T1.aaa = @aaa
GROUP BYaccno
ORDER BYfcode
FETCH NEXT FROM mySQL_cur INTO @accno, @accname
How can I handle this case?
November 16, 2011 at 8:43 am
What is the @aaa parameter? I do not see it in your code example. Where does it come from and how is it set?
Also, are you wanting to add it to the WHERE clause? Something like "... AND @aaa > '' AND T1.aaa=@aaa "
November 16, 2011 at 8:58 am
yes, I would like to add it to where clause
DECLARE mySQL_cur CURSOR FOR
SELECT accno, accname
FROM tb1 T1 (Nolock)
LEFT JOINtb2 T2 (Nolock) ON T1.accno=T2.custcode
WHERE T1.date=@date and
GROUP BYaccno, accname
OPEN mySQL_cur FETCH NEXT FROM mySQL_cur INTO @accno, @accname
WHILE @@FETCH_STATUS=0
BEGIN
SET @nettotal = @total1 - @total2
INSERT INTO tmpTb3
(accno, accname, total1, total2, nettotal)
VALUES (@accno, @accname, @total1, @total2, @nettotal)
INSERT INTOtmpTb3
(accno, accname, total1, total2, nettotal, value, detail)
SELECT accno=@accno, accname=@accname, total1=@total1, total2=@total2, nettotal=@nettotal, value=@value, detail=@detail
FROM tb1 Tb1 (Nolock)
LEFT JOINtb2 Tb2 (Nolock) ON T1.accno=T2.custcode
WHERE T1.date=@date
IF @aaa <> '' [p]AND T1.aaa = @aaa[/p]
GROUP BYaccno
ORDER BYfcode
FETCH NEXT FROM mySQL_cur INTO @accno, @accname
November 16, 2011 at 9:28 am
Will the @aaa parameter ever be NULL? You have to check for this case explicitly.
Try changing the IF clause to:
AND ( (@aaa > '' AND T1.aaa=@aaa) OR (@aaa='') )
EDIT: The ">" is actually a greater than symbol. Even though it previews correctly, the symbol is converted once posted.
November 16, 2011 at 9:32 am
OK, I realized the formatting aspect of greater than symbols is not coming across correctly, even in my "EDIT" section.
One more try to not cause any more confusion than I probably already have done.
AND ( (@aaa > '' AND T1.aaa=@aaa) OR (@aaa = '') )
November 16, 2011 at 9:59 am
@SSC-Enthusiastic
Thank you for your helping out.
November 16, 2011 at 3:43 pm
I don't think you need a cursor for this at all. Given the skeleton you posted there is nothing to indicate the need for a cursor.
And why are you using the NOLOCK hint? Do you know what it does and the potential ramifications?
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
If you want to take a crack at getting rid of that cursor, look at the first link in my signature. Post some ddl and sample data and we can have a go at it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply