In the stored below, I am trying to accomplish 3 things.
1, if a user attempts to sign up, check to see if the user has already signed up.
If yes, fire up a message that 'This user has already registered'
If not, then register the user with insert statement.
2, If registration is successful, fire up a message that says, "Registered successfully"
3, If registration is full, put users in the waitingList with INSERT into waitingList.
If registration into waitingList is successully, give a message, "You have been placed on waiting list"
None of this is working.
Your great expert assistance is greatly appreciated.
/****** Object: StoredProcedure [dbo].[sp_SignUp] ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[sp_SignUp]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @error varchar(max)
SET NOCOUNT ON;
if exists(SELECT * FROM tblTrainings WHERE Username = @UserName AND CourseID = @cosID AND LocationID = @locid AND dateId = @dat)
set @error = 'You have already signed up for this training'
raiserror(@ERROR, 12, 1)
DECLARE @seatsAvailable int
SET @seatsAvailable = 0;
SELECT @seatsAvailable =
(select Seating_Capacity - (select count(*) from tblTrainings WHERE CourseId=@cosID )
WHERE LocationId = @locID )
if @seatsAvailable > 0
INSERT INTO tblTrainings (CourseId, LocationId, dateId, username) VALUES (@CosID, @LocID, @dat, @Username)
set @error = Registration is successful'
raiserror(@ERROR, 13, 1)
INSERT INTO tblWaitingList (CourseId, LocationId, dateId, username) VALUES (@CosID, @LocID, @dat, @Username)
set @error = 'You have been placed on the waiting list. You will be immediately notified if a seat is available'
raiserror(@ERROR, 14, 1)
Thanks a lot in advance