/*

The Stored Procedure has a date parameter.

myProc should run in a loop while the date is between '2010-01-05' and '2010-01-25',

so date '2010-01-28' will be skipped.

I dont want to add anything inside the stored procedure proc_student,

would prefer if just running it from a query outside the stored procedure proc_student.

This example works ok, but I am looking for other methods to do the same.

Please view code below and provide any suggestions, thanks.

*/

CREATE TABLE Students (

Id int,

Name varchar(20),

Class int,

Date datetime

)

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

insert into Students values(1, 'Goran',10,'2010-01-05');

insert into Students values(2, 'Nikolai',10,'2010-01-07');

insert into Students values(3, 'Oksana',10,'2010-01-10');

insert into Students values(4, 'Peter',10,'2010-01-17');

insert into Students values(5, 'Steven',10,'2010-01-28');

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

CREATE PROCEDURE proc_students

@Date datetime

AS

Select * from Students

where Date = @Date

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

exec proc_students @Date = '2010-01-17'

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

--Below is the external query to execute Stored Procedure in a Loop:

DECLARE @Date datetime

DECLARE @MaxDate datetime

SET @Date = '2010-01-04'

SET @MaxDate = '2010-01-25'

WHILE @Date < @MaxDate

BEGIN

SET @Date = @Date + 1

IF @Date = '2010-01-05'

OR @Date = '2010-01-07'

OR @Date = '2010-01-10'

OR @Date = '2010-01-17'

OR @Date = '2010-01-25'

EXEC proc_students @Date

END