Please Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Please Help

Author

Topic

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-14 : 04:51:46

Can anyone help with this. 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 new class there should be an 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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-14 : 05:01:51

So what's the problem here?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-14 : 05:06:57

It doesnt work. I am trying to find out why

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-14 : 05:15:05

You are inserting dbo.classes table's record in classes table again..why?

Also what do you mean by it doesn't work? do you get error?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 05:34:58

INSERT INTO dbo.classes
<a field list here would be a good idea>
SELECT

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-14 : 05:35:19

This procedure is used for a gymnasium booking system. The administrator creates a week of classes(in the future)In order to help I want to be able to give them a procedure that takes takes a start date to copy, and copies a full week of listings into the next week or weeks depending on the the number of iterations. The problem here is that the @@IDENTITY is only being used once for some reason and not when each new record is put into the class table. Which means the two other inserts at the bottom of the sp are only ever happening once. I need for each record that goes into the class table - one record to be inserted into the sessions table and one insertion for each new class to be inserted into the slots table.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 05:36:58

Also, use SCOPE_IDENTITY() instead of SELECT @@IDENTITY

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-14 : 05:54:30

Rick what difference does the scope_identity() make. Also looking at my previous post can you tell my why the two inserts (which should be in a bloody trigger but our DBA wont allow!)only insert once and not with every insert into the class table.

Thanks for the help

Phill

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 06:22:17

What field is your identity column in the classes table?

scope_identity() will ensure that you do not get a rouge value in the identity as it limits the processing to the current spid. @@identity will get the last identity inserted in the whole database by anyone.

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-14 : 06:28:10

class_id which is an integer and identity set to yes

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 06:33:54

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

AS

SET NOCOUNT ON
SET DATEFORMAT dmy

DECLARE @counter int, @identity int
SET @counter = 0

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

INSERT INTO dbo.classes (StartDate,
StartTime,
EndTime,
class_def_id,
<datefield>,
<empNoField>,
modify_dte,
modify_emp_no,
is_deleted)
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))

set @identity = SCOPE_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)
set @identity = 0

END
[/code]

What does this give you?
Obviously change the field names.

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-14 : 06:52:29

Changed to this but it still only inserts the single record into sessions and slots

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

AS

SET NOCOUNT ON
SET DATEFORMAT dmy

DECLARE @counter int
DECLARE @id int,@identity int
SET @counter = 0

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

INSERT INTO dbo.classes
(class_dte,
class_start_time,
class_end_time,
class_def_id,
create_dte,
create_emp_no,
modify_dte,
modify_emp_no,
is_deleted)

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))

set @identity = SCOPE_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)
set @identity = 0

END
GO

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 07:27:19

[code]
declare @classes table (class_id int IDENTITY(1,1) not null,
class_dte datetime,
class_start_time datetime,
class_end_time datetime,
class_def_id int,
create_dte datetime,
create_emp_no int,
modify_dte datetime,
modify_emp_no int,
is_deleted bit)

declare @sessions table (
class_id int,
create_emp_no int
)

declare @slots table (session_id int, slots int, create_emp_no int)

insert into @classes (class_dte,
class_start_time,
class_end_time,
class_def_id,
create_dte,
create_emp_no,
modify_dte,
modify_emp_no,
is_deleted)
select getdate(),getdate(),getdate(),1,getdate(),1,getdate(),1,0

DECLARE @counter int
DECLARE @identity int, @empNo char(8), @repetitions int, @startDte datetime
SET @counter = 0

set @repetitions = 3
set @empno = 1
set @startDte = getdate()

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

INSERT INTO @classes
(class_dte,
class_start_time,
class_end_time,
class_def_id,
create_dte,
create_emp_no,
modify_dte,
modify_emp_no,
is_deleted)

SELECT top 1
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
@classes
WHERE (class_dte BETWEEN @startDte and DATEADD(wk,1, @startDte))

set @identity = SCOPE_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)
set @identity = 0

END

select * from @classes
select * from @sessions
select * from @slots
GO
[/code]
There you go, this works.

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-14 : 07:41:44

Thanks matey. Will try this out on Monday. Had to leave early today so reading this from home.

Your da man!!!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 07:55:04

Lucky you, I have another hour to go yet..

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-17 : 05:51:29

Hi again Rick

The sample code that you mentioned above only creates one row. I have created some sample data in my table classes which starts on 02-JAN-2008 to 06-JAN-2008 twenty five rows in all starting from the wednesday to sunday. THe stored procedure that I will paste below copies all the data for this week and pastes it into the next week which gives me 50 rows in all when @repetitions is set to 1.(which is what I want)The only thing that it does not do is for each record that it creates in the classes table - it should create an entry in sessions table and an entry in the slots table. Here is the sp as it stands

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

AS

SET NOCOUNT ON
SET DATEFORMAT dmy

DECLARE @counter int
DECLARE @id int,@identity int
SET @counter = 0

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

INSERT INTO dbo.classes
(class_dte,
class_start_time,
class_end_time,
class_def_id,
create_dte,
create_emp_no,
modify_dte,
modify_emp_no,
is_deleted)

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))

set @identity = SCOPE_IDENTITY()
INSERT INTO sessions(class_id, create_emp_no,create_dte) VALUES (@identity,@empNo, getDate())
INSERT INTO slots(session_id, slots, create_emp_no,create_dte) VALUES(@identity,10,@empNo,getDate())
set @identity = 0

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

What the sp is meant to do is give the administrator a way of bulk loading the diary. Normaly, to create a new class they would run this procedure - which as you can see updates the class table and then also the other two corresponding tables

CREATE PROCEDURE [DBO].[ins_new_class] @dte datetime, @start datetime, @end datetime, @id int, @empNo char(8)

AS

INSERT INTO classes
(class_dte,
class_start_time,
class_end_time,
class_def_id,
create_emp_no)
VALUES
(@dte,
@start,
@end,
@id,
@empNo)

Select @@IDENTITY

INSERT INTO sessions(class_id, create_emp_no,create_dte) VALUES (@@IDENTITY,@empNo,getDate())

INSERT INTO slots(session_id, slots, create_emp_no,create_dte) VALUES(@@IDENTITY,10,@empNo,getDate())
GO

Thanks for your help in advance

matty
Posting Yak Master

161 Posts

Posted - 2007-12-17 : 06:16:12

is this what you need?

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

AS

SET NOCOUNT ON
SET DATEFORMAT dmy

DECLARE @counter int, @identity int
delclare @temp table
( class_id int
)

SET @counter = 0

WHILE @counter < @repetitions
BEGIN
SET @counter = @counter + 1
print 'The counter is ' + cast(@counter as char)
INSERT INTO dbo.classes (StartDate,
StartTime,
EndTime,
class_def_id,
<datefield>,
<empNoField>,
modify_dte,
modify_emp_no,
is_deleted)
output inserted.class_id into @temp(class_id)
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))

INSERT INTO sessions(class_id, create_emp_no) select class_id,@empNo from @temp
INSERT INTO slots(session_id, slots, create_emp_no) select class_id,@empNo from @temp
set @identity = 0

END

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-17 : 07:19:16

Amended it slightly to use proper field names but getting errors

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

AS

SET NOCOUNT ON
SET DATEFORMAT dmy

DECLARE @counter int, @identity int
DECLARE @temp table( class_id int)

SET @counter = 0

WHILE @counter < @repetitions
BEGIN
SET @counter = @counter + 1
print 'The counter is ' + cast(@counter as char)
INSERT INTO dbo.classes
(class_start_dte,
class_start_time,
class_end_time,
class_def_id,
create_dte,
create_emp_no,
modify_dte,
modify_emp_no,
is_deleted,

OUTPUT inserted.class_id into @temp(class_id)
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))

INSERT INTO sessions(class_id, create_emp_no,create_dte) select class_id,@empNo,getDate() from @temp
INSERT INTO slots(session_id, slots, create_emp_no,create_dte) select class_id,@empNo, getDate() from @temp
set @identity = 0

END

Getting and error around the (OUTPUT inserted.class_id) part and its complaining that the insert statement contains fewer items than the insert list. But there are nine of each

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-17 : 07:33:24

Sorry the error is only arround the output inserted.class_id into @temp(class_id)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 07:34:31

[code]CREATE PROCEDURE dbo.Tester
(
@EmpNo CHAR(8),
@Repetitions INT,
@StartDte DATETIME
)
AS

SET NOCOUNT ON

SET @StartDte = DATEDIFF(DAY, '19000101', @StartDte)

DECLARE @MaxID INT

BEGIN TRAN

SELECT @MaxID = MAX(Class_ID)
FROM dbo.Classes

INSERT dbo.Classes
(
class_start_dte,
class_start_time,
class_end_time,
class_def_id,
create_dte,
create_emp_no,
modify_dte,
modify_emp_no,
is_deleted
)
SELECT DATEADD(wk, n.Number, c.Class_Dte) AS StartDate,
DATEADD(wk, n.Number, c.Class_Start_Time) AS StartTime,
DATEADD(wk, n.Number, c.Class_End_Time) AS EndTime,
c.Class_Def_ID,
CURRENT_TIMESTAMP,
@EmpNo,
c.Modify_Dte,
c.Modify_Emp_No,
c.Is_Deleted
FROM dbo.Classes AS c
CROSS JOIN (
SELECT Number
FROM master..spt_values
WHERE Type = 'p'
AND Number < @Repetitions
) AS n
WHERE c.Class_Dte BETWEEN @StartDte AND DATEADD(wk, 1, @StartDte)

INSERT [Sessions]
(
Class_ID,
Create_Emp_No,
Create_Dte
)
SELECT Class_ID,
Create_Emp_No,
Create_Dte
FROM dbo.Classes
WHERE ClassID > @MaxID

INSERT Slots
(
Session_ID,
Slots,
Create_Emp_No,
Create_Dte
)
SELECT Class_ID,
NULL, -- Insert value of column name here for SLOTS
Create_Emp_No,
Create_Dte
FROM dbo.Classes
WHERE ClassID > @MaxID

COMMIT TRAN[/code]

E 12°55'05.25"
N 56°04'39.16"

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2007-12-17 : 08:10:37

Hi Peso, almost perfect. the first part needs to copy (using the @startDate)all the entries for one week in the classes table and then copy those entries one week ahead(if @repetitions is set to 1)into the same table. the current sp below does exaclty what I need for the two related tables sessions and slots. But the classes table is not moving on the selected rows one week. In a previous version - that was the only thing that was working!!! It used this
WHERE
(class_dte BETWEEN @startDte and DATEADD(wk,1, @startDte))

I marvel at this code and when it finally works as I need I will need to sit down and comment it out as it is far beyond my pitiful sql level. Could you please show me where I need to ammend the code to get the class table insert to incorporate the adding a week bit

CREATE PROCEDURE dbo.Tester2
(
@EmpNo CHAR(8),
@Repetitions INT,
@StartDte DATETIME
)
AS

SET NOCOUNT ON

SET @StartDte = DATEDIFF(DAY, '19000101', @StartDte)

DECLARE @MaxID INT

BEGIN TRAN

SELECT @MaxID = MAX(Class_ID)
FROM dbo.Classes

INSERT dbo.Classes
(
class_dte,
class_start_time,
class_end_time,
class_def_id,
create_dte,
create_emp_no,
modify_dte,
modify_emp_no,
is_deleted
)
SELECT DATEADD(wk, n.Number, c.Class_Dte) AS StartDate,
DATEADD(wk, n.Number, c.Class_Start_Time) AS StartTime,
DATEADD(wk, n.Number, c.Class_End_Time) AS EndTime,
c.Class_Def_ID,
CURRENT_TIMESTAMP,
@EmpNo,
c.Modify_Dte,
c.Modify_Emp_No,
c.Is_Deleted
FROM dbo.Classes AS c
CROSS JOIN (
SELECT Number
FROM master..spt_values
WHERE Type = 'p'
AND Number < @Repetitions
) AS n
WHERE c.Class_Dte BETWEEN @StartDte AND DATEADD(wk, 1, @StartDte)

INSERT [Sessions]
(
class_id,
create_emp_no,
create_dte
)
SELECT class_id,
create_emp_no,
create_dte
FROM dbo.classes
WHERE class_id > @MaxID

INSERT Slots
(
session_id,
slots,
create_emp_no,
create_dte
)
SELECT class_id,
10, -- Insert value of column name here for SLOTS
create_emp_no,
create_dte
FROM dbo.classes
WHERE class_id > @MaxID

COMMIT TRAN
GO

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources