Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

temp table in stored procedure

Status
Not open for further replies.

paraglidersd

Programmer
May 21, 2008
7
US
am pretty inexperienced when it comes to sql, but I am an experienced engineer.

Want to create a stored procedure. Within that procedure I am going to create a temp table with certain attributes read in from 2 other permanent tables. After that, within the stored procedure, I want to read the resulting rows of the temp table one by one (one of the attributes will be a key that I will use to access yet another permanent table). I dont know how to do this.

select key1, att1 into #temp_table
from permTable1
where
key > xx
date > 999999

insert #temp_table
select key1, att1 from permTable1History
where
key > xx
date > 999999

....this is where I am stuck....how do I now read the contents of the #temp_table within the stored procedure one by one, to pull out that key attribute, to use to read from another permanent table?

thanks,
Bill
 
This looks like Microsoft SQL Server. As such, you should be posting this in forum183.

If I am right, and this is a Miscorosft SQL Server question, then it sounds like you are describing a cursor. [google]Microsoft SQL Server Cursor[/google].

99 times out of a hundred, a cursor is going to be slower than a set based operation. Also.... 99 cursors out of 100 can be re-written so that a cursor is not used.

If you describe your situation a little more, perhaps we can show you how to retrieve your data without using a cursor.

Specifically this....

>> to pull out that key attribute, to use to read from another permanent table?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
First, and foremost, thank you for reading my post.

I dont think its microsoft. In our system, we are using Sybase. We have a series of C programs that manipulate (read/write/delete) from our database. To speed some of the database access up, we created some stored procedures that are loaded and 'exec'd from the C programs. I looked at some of our legacy stored procedures, and none of them do what I described. Some of the legacy procedures create temp tables, but none attempt to then read from the data gathered into that temp table within the stored procedure.

Am I posting in the right place? If not, I apologize.
 
parag. Sybase and SQL Server both use Transact SQL. Most of the things you can do on SQL Server (prior to 2005) you can also do in Sybase. So reading the SQL Server forum wont do you any harm.

But you should really post this on forum187

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top