how to use dynamic sql with cursor in s. proc? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

how to use dynamic sql with cursor in s. proc?

Author

Topic

maloy
Starting Member

19 Posts

Posted - 2002-03-02 : 06:46:36

I have a stored procedure which performs some operations on a series of ~15 tables named as per_yy_hist where yy is the year e.g. 93 for 1993 and so on. The operations involve lot of cleansing and comparisons , so i've used a cursor which process one row at a time.

Because the names of the tables change continuously, I'm unable to do use the same procedure for all the tables.

How do I use dynamic sql/sp_executesql to use the same query for selecting records in the same cursor and process with the same procedure?

Please help.
Thanks

Maloy

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-02 : 07:01:02

You might like to post your "cleansing procedures". Chances are someone here will rephrase them for you using set notation rather than cursors, and your dynamic sql problems will evaporate.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-02 : 09:45:07

I completely agree. If you can post your code and what exactly you're doing/looking to accomplish we can probably provide a better solution.

maloy
Starting Member

19 Posts

Posted - 2002-03-02 : 09:53:28

Thanks. But the code itself is lengthy and uses other user-defined functions and some other stored procedures related to the business domain; so it'd not be possible to post all that here.
Even if getting it into a cursor seems impossible, is there any way I can get the records into some temp table and loop through it like I do with a cursor.
Please reply.

Maloy

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-03-02 : 12:13:23

Well, it's really hard to talk about this without an example of the code. But if I understand you correctly, why not:

1) Create a temp table with the same schema as your real tables
2) Copy data from a real table into the temp table
3) Call your stored procedure and have it process the temp table
4) truncate the real table
5) Copy the data back from the temp table

Alternatively, you could use dynamic SQL to create and then drop your stored procedure one for each table.

However, the fact that you're using a cursor should definitely be a warning sign that there's almost certainly a better way of doing things. When I started reading SQLTeam, 10 of my 260 SP's used cursors. Today, none of them do, and my app is much higher performance for it.

Cheers
-b

maloy
Starting Member

19 Posts

Posted - 2002-03-03 : 22:28:51

Thanks much, I wish I could've not used cursors. But the powers that be...:(
Can u give a code example to show how I can create and drop stored procedure using dynamic sql?

Thanks

Maloy

Nazim
A custom title

1408 Posts

Posted - 2002-03-03 : 23:33:07

Huh? I thought people are intrested in getting their job done in most efficent manner. whatever might be the causes , i think they are crazy trying to stick to cursors. i will still suggest you to post atleast the cursor relevant code . and Am sure team members will help you.

quote:

Thanks much, I wish I could've not used cursors. But the powers that be...:(

About your Dynamic Sql for creating and dropping a table.

declare @sql as varchar(1000)
select @sql='create procedure NOCURSORS as select * from inv_header'
exec(@sql)
select @sql='drop procedure NOCURSORS'
exec(@sql)

HTH

--------------------------------------------------------------

Edited by - Nazim on 03/03/2002 23:38:00

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-04 : 06:27:00

The follwing lets you loop through a table - in most case though there is no need to loop through tables (via cursors or pseudo cursors as here):-

-- Loop through #tablename (which has a field called id which is in the order you wish to loop through)
-- The @field_name var gets each occurrence of field_name in the table in one big string

declare @id_loop int
declare @maxid int
declare @field_name varchar(8000)
set @field_name=''

select @id_loop = min(id)-1, @maxid = max(id) from #table_name

while @id_loop < @maxid
    begin
	select @id_loop = min(id) from #table_name where id > @id_loop
	select @field_name=@field_name+field_name
		from #table_name where id=@id_loop
    end

============
The Dabbler!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources