Cursor Results in Stored Proc - 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)

Cursor Results in Stored Proc

Author

Topic

NIRVANA
Starting Member

4 Posts

Posted - 2009-02-25 : 21:30:34

I have a query in a stored proc lets say the following

select * from Business where unit = ???

Now in my stored_proc i have a query like the one above in a cursor and all is working fine, for each result i insert into a temp table(as i am doing some extra manipulation for each type of record) and then do a slect on the table to get the data.

Tis all works fine but i would like the results to be displayed like a normal query you may run in a stored proc(minus cursor) the reason for this is

This proc will be on a reporting server (2005) and may be a case when users run the reports at the same time hence the possible clash with temp tables.

Now i have tried to assign the data to variables and then print the variables which prints it, but if you try and retireve the recordset from a custom VB app or Reprting Server the recordset is blank, but if u look at the results in Query Analyser they are there but not in the same format as a normal query.

I would like the results to be like normal query results, this way i can dispense with tmp tables and the reports can be run at the same time.

Is this possible.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-02-26 : 11:12:22

I vaguely understand what you are asking.. can you post some code and highlight the areas where you need help?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

NIRVANA
Starting Member

4 Posts

Posted - 2009-02-26 : 17:34:03

OK, something like this

Declare Emp_cursor cursor for
Select * from Employee where business = ???

open Emp_Cursor
fetch next from Emp_Cursor into @variables

Whilse @@fetch_Status 0

begin
--In here i do additional calculations with each row of data along with another Cursor to select specfic data based on the data set from the 1st cursor

Insert into Temp_Table values (data)

close cursor
deallocate cursor

This is just q uick mock up of the stored-proc, like i said it all works fine, but instead of inserting into a temp table i want to have the results displayed like a normal query in a stored proc(without the cursor), i have tried assigning each data element to a variable and printing it using print but the recordset appears blank when u try access the data from an external app like vb or reports server.

end

NIRVANA
Starting Member

4 Posts

Posted - 2009-02-26 : 17:34:05

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-26 : 19:16:44

quote:
for each result i insert into a temp table(as i am doing some extra manipulation for each type of record) and then do a slect on the table to get the data. Tis all works fine but i would like the results to be displayed like a normal query you may run in a stored proc(minus cursor)

If you are inserting results into a temp table, and then selecting from that temp table, then it will "display like a normal query". I'm not clear what you mean by this or why it doesn't appear the way you want. I think you'll need to post some sample data to show what you mean.

quote:
This proc will be on a reporting server (2005) and may be a case when users run the reports at the same time hence the possible clash with temp tables.

Temp tables in SQL Server (prefixed with #) are isolated to the session that created them. Multiple sessions cannot interfere with one another's temp tables. Global temp tables (prefixed with ##) and regular tables can become locked or blocked by multiple sessions. Unless you are actually experiencing this problem though, you shouldn't be rewriting your code to avoid it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources