asked on

How do I get messages to work on my stored proc?

Hi mates,

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.

USE [STRAINING]
GO
/****** Object:  StoredProcedure [dbo].[sp_SignUp]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[sp_SignUp]
	@cosID int,
	@locid int,
	@dat int,
	@UserName varchar(50)
AS
BEGIN
-- 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)
begin
set @error = 'You have already signed up for this training'
raiserror(@ERROR, 12, 1)
end
		else
  DECLARE @seatsAvailable int
   SET @seatsAvailable = 0;
  SELECT @seatsAvailable =
       (select Seating_Capacity - (select count(*) from tblTrainings WHERE CourseId=@cosID )
        FROM tblLocations
        WHERE LocationId = @locID )
     
  if @seatsAvailable > 0
begin
    INSERT INTO tblTrainings (CourseId, LocationId, dateId, username) VALUES (@CosID, @LocID, @dat, @Username)
set @error = Registration is successful'
raiserror(@ERROR, 13, 1)

end
 else
begin
    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)
end
END

Open in new window

Thanks a lot in advance

ASKER CERTIFIED SOLUTION

Nem Schlecht

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sammySeltzer

ASKER

You are absolutely right about a few things.

1, I did change to OUTPUT

Then I removed the raiseERROr completely.

Almost everything is working now.

Only thing not working is this:

set @error = 'You have already signed up for this training'

Any ideas what's wrong?

Nem Schlecht

First off, although it does *not* matter, I would not switch the case around on your variable names.

Ie - use @ERROR or @error, but don't use both. SQL is case *insensitive*, but still, it's a bad idea (somebody else might they they are different).

Can you post the latest version of your code?

sammySeltzer

ASKER

ALTER PROCEDURE [dbo].[sp_Register]
	@cosID int,
	@locid int,
	@dat int,
	@UserName varchar(50),
    @ERROR VARCHAR(1000) OUT

AS
BEGIN
-- 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)
  BEGIN
	SET @ERROR = 'You have already signed up for this training'
  END
ELSE
  DECLARE @seatsAvailable int
   SET @seatsAvailable = 0;
  SELECT @seatsAvailable =
       (select Seating_Capacity - (select count(*) from tblTrainings WHERE CourseId=@cosID )
        FROM tblLocations
        WHERE LocationId = @locID )
     
If @seatsAvailable > 0
  BEGIN
    INSERT INTO tblTrainings (CourseId, LocationId, dateId, username) VALUES (@CosID, @LocID, @dat, @Username)
     SET @ERROR = 'You have been registered for this class'
  END
ELSE
  BEGIN
    INSERT INTO tblWaitingList (CourseId, LocationId, dateId, username) VALUES (@CosID, @LocID, @dat, @Username)
     SET @ERROR = 'Sorry, this class is full. However, you have been placed on waiting list for this. Registrants will be notified on a first-come, first-serve basis'
  END
END

Open in new window

Nem Schlecht

If on line 14, immediately after your "SET NOCOUNT ON", you add this line:

SET @ERROR = 'Testing - client should not see this';

And try your app again. Does your application see this string returned?

If not, what does your calling app get back from this proc for @ERROR?

sammySeltzer

ASKER

No, it didn't show up.

Nem Schlecht

You should be getting something back. What are you calling this stored procedure from? C#? Visual Basic? Another stored procedure?

Set whatever variable you are passing to it as something, and then see afterwards if it was overwritten or if it is still the same.

For example (pseudo code):

var casid = 'whatever';
var locid = 'there'
var dat = 'then'
var username = 'thatguy'
var error = 'this string should get overwritten';
Sql.Execute(sp_Register, cosid, locid, dat, username, error OUT);
WriteLine('Error is now' + error);

Open in new window

sammySeltzer

ASKER

I figured out my problem.

Before I posted this question, I tried to put a BEGIN...END just after the first ELSE.

However, because I had a custom error on my .net app, I was getting the custom error message rather than the message on the stored proc.

I commented out the custom error message and it is working fine now.

Bottom line was I had to put a BEGIN/END block right after the first ELSE statement on line 19.

Thanks for trying to help.