Click here to Skip to main content
15,880,796 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
Please check my below procedure. Expected output is 14 rows. I get it when I run the inner query(inside cursor). But when I run the entire procedure,it gives 28 rows,i.e one set 1 to 14 again same result set from 1 to 14. Expected set of 14 rows is shown twice one below other.Pls suggest the correction...thanku

SQL
<pre>USE [Opening1]
GO
/****** Object:  StoredProcedure [dbo].[HSR] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROC [dbo].[Sample]
AS
BEGIN
Declare @Showroomcode varchar(50),
@Startdate date,
@Stockno int, 
@Servicetag varchar(50),
@TransType int,
@Qty int,
@OP int
 
BEGIN
DECLARE Sam Cursor
STATIC FOR 
select a.showroomcode,a.startdate from 
(select distinct showroomcode,startdate from IMTable) a 
left join (select distinct Showroomcode,Startdate from HSR_OP_Bal ) b
on a.showroomcode=b.showroomcode
and   a.StartDate=b.StartDate
where b.showroomcode is null and a.Startdate='2015-09-01'
order by a.Startdate

OPEN Sam 
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM Sam  INTO @Showroomcode,@Startdate
WHILE @@FETCH_STATUS = 0
begin

Select Showroomcode,Stockno,Servicetag,Sum(Qty)as Qty
from
(
	( Select H.showroomcode,H.Stockno,H.Servicetag,H.OP as Qty from HSR_OP_Bal H,
	   (Select showroomcode ,Stockno,Startdate from IMTable) I
	   
	where H.Showroomcode=I.Showroomcode and H.Stockno=I.Stockno 
	and I.Startdate='2015-09-01' ) 

union all 

	(  select a.Showroomcode,a.Stockno,a.Servicetag,
		(case when TransType in (1100,1300) then Qty*1
			else  Qty* -1
		  end ) as Qty  from ConsIMEItrndata a, 
  
(Select  Showroomcode,Stockno,Startdate from IMTable) B
	 
	where A.Showroomcode=B.Showroomcode and A.Stockno=B.Stockno
	and B.Startdate ='2015-09-01'  and a.Transdate <'2015-09-01' ) )T
	
Group by Showroomcode,Stockno,Servicetag
FETCH NEXT FROM Sam  INTO @Showroomcode,@Startdate
 end
  end


    CLOSE Sam 
	DEALLOCATE Sam 
  end
  end
Posted
Comments
Herman<T>.Instance 15-Sep-15 6:07am    
And what happens if you start your query with SELECT DISTINCT?

1 solution

We don't have your data nor the knowledge about your tables. Because of this it's impossible t say what is logically wrong with the statements.

However, what you can do is to run the cursor statement and see what is duplicated. For example take the statement
SQL
Select Showroomcode,Stockno,Servicetag,Sum(Qty)as Qty
from
(
	( Select H.showroomcode,H.Stockno,H.Servicetag,H.OP as Qty from HSR_OP_Bal H,
	   (Select showroomcode ,Stockno,Startdate from IMTable) I
	   
	where H.Showroomcode=I.Showroomcode and H.Stockno=I.Stockno 
	and I.Startdate='2015-09-01' ) 
 
union all 
 
	(  select a.Showroomcode,a.Stockno,a.Servicetag,
		(case when TransType in (1100,1300) then Qty*1
			else  Qty* -1
		  end ) as Qty  from ConsIMEItrndata a, 
  
(Select  Showroomcode,Stockno,Startdate from IMTable) B
	 
	where A.Showroomcode=B.Showroomcode and A.Stockno=B.Stockno
	and B.Startdate ='2015-09-01'  and a.Transdate <'2015-09-01' ) )T
	
Group by Showroomcode,Stockno,Servicetag

and run it in SSMS. You can replace the columns with an asterisk so you would more easily see what data is repeated and what is different on each row. This helps you to see what causes the duplication. On probable cause is the UNION structure if the same data is returned from both sides of the UNION.
 
Share this answer
 
v2
Comments
Member 11377180 15-Sep-15 7:06am    
Hi..This is what i mentioned.When i run the statements you mentioned it will give me 14 rows(my desired output) but when i run entire thing it repeats result set. For example,
my result set is 3 rows. So the output wud be like:
Fruits:
Apple
Mango
Papaya

Fruits:
Apple
Mango
Papaya
Member 11377180 15-Sep-15 7:07am    
I feel something is wrong with cursor but not getting how to correct it
Wendelius 15-Sep-15 7:44am    
Does the cursor Sam return 2 rows? If it does, it looks like your loop will be run twice thus running the statement with union twice.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900