Exec procedure - 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

General SQL Server Forums

New to SQL Server Programming

Exec procedure

Author

Topic

Yellowdog
Starting Member

34 Posts

Posted - 2009-07-17 : 10:50:39

I have a procedure I need to run that takes one parameter.

I need to run that procedure multiple times based on a query that is going to return multiple rows with the parameter that needs to be passed to the procedure.


exec delete_record (record_id)
select record_id from files where expiration_date <= getdate()

So my question is how can I do this without running it every time, basically I need this to be automated.

Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 11:19:31

To execute a stored procedure multiple times, you have to use some form of looping to do that. Like While Loop or cursor.

A better solution is to perform the deletion inside your delete_record stored procedure. Change your delete_record procedure to handle the deletion of records where expiration_date <= getdate(). The performance will be much better this way compare to the looping method

KH
[spoiler]Time is always against us[/spoiler]

Yellowdog
Starting Member

34 Posts

Posted - 2009-07-17 : 11:32:29

I was afraid you were going to say that.

The procedure itself drops multiple tables and records from multiple tables, I was hoping to avoid writing a whole new procedure. The above procedure is already in use and I was hoping to just reuse the code but I guess it is what it is.

Thanks for your help!

Yellowdog
Starting Member

34 Posts

Posted - 2009-07-17 : 12:24:49

I am not too familiar with while loops in sql so just looking for some conformation here.


create procedure delete_expired
as 

declare @file_delete int

while ((select max(file_id) from files where expiration_date <= getdate()) is not null)
begin
	set @file_delete = (select max(file_id) from files where expiration_date <= getdate())
	
	exec delete_record @file_delete
end

This should work correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:11:07

it should be


create procedure delete_expired
as 

declare @file_delete int
select @file_delete =  max(file_id) 
from files 
where expiration_date <= getdate()

while @file_delete is not null
begin
	
exec delete_record @file_delete
 select @file_delete =  max(file_id) 
from files 
where expiration_date <= @file_delete
end

Yellowdog
Starting Member

34 Posts

Posted - 2009-07-17 : 13:42:12

Thanks a bunch!

So mistakes that I made were
1 set the test expression before the test, in other words do not include the select statement in the evaluation.
2 no parenthesis are necessary in the test expression.
3 use select statements instead of set for a more concise statement.

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:46:41

quote:
Originally posted by Yellowdog

Thanks a bunch!

So mistakes that I made were
1 set the test expression before the test, in other words do not include the select statement in the evaluation.
2 no parenthesis are necessary in the test expression.
3 use select statements instead of set for a more concise statement.

Cheers

1. yes and make sure you correctly take next value inside loop
2.yes
3.you can use set also. but i prefer select while assigning value of variable from query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources