Recursive Statements,String like object,

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)

Recursive Statements,String like object,

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-13 : 08:04:37

Vishal writes "Hi there,
I have table of 200000 entries. each entry is inter linked with another and they are in form of tree, expanding in downwords style. i have to start selecting from one entry and what ever result comes has to be used in further selection.

it gives me error after 31 times selection of limitation of sql nested level upto 32.

2 question .

Is there anything like String object(java) in sql2000. purpose of asking is ,when i select somthing from table ,what ever result comes, i want use it for further selection using

{select * from table1 where id in(a,b,c) }where a ,b ,c are the result of previous query and had been concatenated but assigning to variable varchar(40) gives errors when tries use
with above mentioned query.

for your further assistence i have pasted the code

CREATE proc sunrise7
@xref varchar(4000),@xcounter integer
as
declare @counter integer
declare @userid VARCHAR(4000)
declare @Tuserid varchar(4000)
set @counter = @xcounter;
print ' Counter '+cast(@counter as varchar(40))+'@xref '+@xref
declare cursor12 cursor for select userid from stage0 where referid in (@xref) order by userid

begin
open cursor12
FETCH cursor12 into @userid
while(@@FETCH_STATUS = 0)
begin

set @counter = @counter+1;
FETCH cursor12 into @userid
set @Tuserid = @Tuserid+@userid+',';
print 'Counter **** total user id '+ @Tuserid
end
close cursor12
DEALLOCATE cursor12
set @Tuserid = substring(@Tuserid,0,Datalength(@Tuserid));
execute sunrise7 @Tuserid,@counter
end

regards

Vishal Bhatia"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-13 : 08:06:24

I'm hoping someday they remove cursors from Transact-SQL:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9978

There's two solutions in there that will give you the results you need, but you might have to modify them. You ABSOLUTELY DO NOT need cursors.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-13 : 12:44:29

quote:

I'm hoping someday they remove cursors from Transact-SQL:

But Rob, if they did that, then what would nr have to rail against other than cold fizzy beer?

------------------------
GENERAL-ly speaking...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources