stored procedure help.. - 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)

stored procedure help..

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2007-09-19 : 08:19:10

My Sp is something like this.

declare c1 cursor for
select oper_id form <table_name>
open c1
fetch next from c1 INTO <variable1>

while (@@FETCH_STATUS <> -1)

begin
if variable1 = 1
begin
select statement1
end else
if variable1 = 2
begin
select statement2
end
fetch next from c1 INTO <variable1>

Insert into <table_name> values ()
close c1
deallocate c1

the "select statement1" returns one row , and the insert statement inserts one row.

now the "select statement2" returns for example, 5 rows.
my problem is how the insert statement is going to insert the 5 rows.
How can i use a loop in this case?

any help, suggestion is appreciated.

thanks and regards.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-19 : 08:53:08

Not really sure what you are doing but maybe

Insert into <table_name>
select statement

is what you need.

==========================================
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.

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2007-09-20 : 00:47:48

nr
thanks for replying.

I would like to clarify myself a little bit more.

i am executing a query

select oper_id from mstoperation

this query will return numbers from 1 to 5 respectively.
I have to execute a query based on the value of each of the oper_id values.
that is, when oper_id =1 a particular select statement will be executed.
when oper_id =2 another select statement will be executed. and so on.

Each of these select statements will return single or multiple rows.

And i have to insert those rows in a temporary table.
My problem is when any select statement returns a single row the insert will work fine. But what happens when multiple rows are returned ?

the insert statement will insert a single row if i do not use any loop.

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources