Problem In This Code

[pbeeke]

Can anyone fix this for me. I know that I can achieve what I want using triggers but for some reason our DBA will not allow them. The first part of the code takes a weeks classes and inserts a carbon copy into the table a number of times using a paramater repetitions. On creation of each class there should be and insert into a sessions table and an insert into a slots table. Any help would be great.

CREATE PROCEDURE [dbo].[ins_multiple_weeks] @empNo char(8), @repetitions int, @startDte datetime

AS

SET NOCOUNT ON
SET DATEFORMAT dmy

DECLARE @counter int
SET @counter = 0

WHILE @counter < @repetitions
BEGIN
SET @counter = @counter + 1
print 'The counter is ' + cast(@counter as char)

INSERT INTO dbo.classes

SELECT
DATEADD(wk, @counter, class_dte) AS StartDate,
DATEADD(wk, @counter, class_start_time) AS StartTime,
DATEADD(wk, @counter, class_end_time) AS EndTime,
class_def_id,
getDate(),
@empNo,
modify_dte,
modify_emp_no,
is_deleted
FROM
dbo.classes

WHERE
(class_dte BETWEEN @startDte and DATEADD(wk,1, @startDte))

SELECT @@IDENTITY
INSERT INTO sessions(class_id, create_emp_no) VALUES (@@IDENTITY,@empNo)
INSERT INTO slots(session_id, slots, create_emp_no) VALUES(@@IDENTITY,10,@empNo)




END
GO