Wrong Recordset - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Wrong Recordset

Author

Topic

bubu80
Starting Member

4 Posts

Posted - 2005-05-23 : 09:11:25

Hi all,


DECLARE @CNT INT
DECLARE @TOT INT
SET @CNT = 0
SET @TOT = (SELECT COUNT(*) FROM r_ord WHERE  n_doc = 9)
WHILE @CNT <= @TOT
    BEGIN            
         SELECT Item,Desc
         FROM r_ord where Linea = @CNT AND doc_ref = 9
             UNION ALL
         SELECT Item,Desc
         FROM r_off WHERE Line= @CNT and n_doc= 9
         SET @CNT = @CNT + 1
    END

I expected only 1 recordset ... but the returned more recordset
Tables
Table1


col1       col2               
---------------               
A            2              
B            1                                                     
C            3

Table2


col1       col2
------------------
A            3
B            2
C            4

The recordset that I expected it is this


col1     col2
A         2
A         3
B         1
B         2
C         3
C         4
6 rows affected

But the query returns these recordsets ...


col1     col2
A         2
A         3
2 rows affected

col1 col2


B         1
B         2
2 rows affected

col1 col2


C         3
C         4
2 rows affected

Where is my big wrong??
thanks a lot...
sorry for my english

See u

--------------------------
Sorry for my bad english

I Learn..I Learn..I Learn..

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-23 : 09:32:38

You have a loop with a select inside it. Each time the select is executed it will produce a recordset.
Either change the select to an insert into a temp table and select after the loop or get rid of the loop and mmake it a single query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-23 : 09:57:55

As nr suggessted, assign the Select statement inside loop to the variable and execute that query after END statement

DECLARE @sql varchar(2000)
DECLARE @CNT INT
DECLARE @TOT INT
SET @sql=''
SET @CNT = 0
SET @TOT = (SELECT COUNT(*) FROM @t WHERE n_doc = 9)
WHILE @CNT <= @TOT
BEGIN
set @sql=' SELECT Item,Desc
FROM @t where Linea = @CNT AND doc_ref = 9
UNION ALL
SELECT Item,Desc
FROM r_off WHERE Line= @CNT and n_doc= 9 Union all '
SET @CNT = @CNT + 1
END
set @sql=left(@sql,len(@sql)-10)
exec (@sql)

Madhivanan

Failing to plan is Planning to fail

bubu80
Starting Member

4 Posts

Posted - 2005-05-23 : 12:38:43

Thanks a lot
I love this Forum

--------------------------
Sorry for my bad english

I Learn..I Learn..I Learn..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources