Script works to a point! - 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

Script works to a point!

Author

Topic

DavisK
Starting Member

6 Posts

Posted - 2005-11-23 : 10:23:50

Background: I have a reservations database that cannot have duplicate reservations for the same piece of equipment at the same time. Two main tables are involved; the first one (PR_Add_Record) is where the end-user enters the request. This record is then compared to the historical table (PR_Reservations) to check for conflicts. If there is a conflict then a message should print indicating a conflict; if no conflict exists then the record from PR_Add_Record should be appended to the PR_Reservations and the PR_Add_Record table should be cleared.

The following code will select records from the PR_Reservations table appropriately but nothing seems to happen after that; the code seems to stop at the "If @@ROWCount = 0". What's wrong?

CREATE PROCEDURE [dbo].[spInsertRows] AS

Select * from PR_Add_Record, PR_Reservations
Where PR_Add_Record.resource_id = PR_Reservations.resource_id
and PR_Add_Record.start_time Between
PR_Reservations.start_time and PR_Reservations.end_time
or PR_Add_Record.end_time Between
PR_Reservations.start_time and PR_Reservations.end_time

If @@RowCount <>0

Print 'Sorry, that piece of equipment has already been reserved within the time frame requested.'

Else
Insert into dbo.PR_Reservations
(resource_id,
location,
username,
start_time,
end_time)
SELECT
resource_id,
location,
username,
start_time,
end_time
From dbo.PR_Add_Record
Delete from dbo.PR_Add_Record
GO

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-23 : 10:36:42

if it selects something you should get executed

If @@RowCount <>0
Print 'Sorry, that piece of equipment has already been reserved within the time frame requested.'
Delete from dbo.PR_Add_Record

Maybe it's getting blocked trying to deliver the resultset from the select?

Are you sure you don't want something more like

if exists (
Select * from PR_Add_Record, PR_Reservations
Where PR_Add_Record.resource_id = PR_Reservations.resource_id
and (PR_Add_Record.start_time Between
PR_Reservations.start_time and PR_Reservations.end_time
or PR_Add_Record.end_time Between
PR_Reservations.start_time and PR_Reservations.end_time)
)
begin
Insert into dbo.PR_Reservations
(resource_id,
location,
username,
start_time,
end_time)
SELECT
resource_id,
location,
username,
start_time,
end_time
From dbo.PR_Add_Record

Select * from PR_Add_Record, PR_Reservations
Where PR_Add_Record.resource_id = PR_Reservations.resource_id
and (PR_Add_Record.start_time Between
PR_Reservations.start_time and PR_Reservations.end_time
or PR_Add_Record.end_time Between
PR_Reservations.start_time and PR_Reservations.end_time)

Delete from dbo.PR_Add_Record
end
else
begin
select 'Sorry, that piece of equipment has already been reserved within the time frame requested.'
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DavisK
Starting Member

6 Posts

Posted - 2005-11-23 : 10:45:22

"if exists (
Select * from PR_Add_Record, PR_Reservations
Where PR_Add_Record.resource_id = PR_Reservations.resource_id
and (PR_Add_Record.start_time Between
PR_Reservations.start_time and PR_Reservations.end_time
or PR_Add_Record.end_time Between
PR_Reservations.start_time and PR_Reservations.end_time)
)
begin
Insert into dbo.PR_Reservations "

Won't this insert a duplicate time reservation into the PR_Reservations table before checking for conflicts?

Thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-23 : 10:57:34

Sorry - should be if not exists ( ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DavisK
Starting Member

6 Posts

Posted - 2005-11-23 : 11:15:32

Perfect! Thanks very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources