Solved

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

Posted on 2013-06-18
8
206 Views
Last Modified: 2013-06-18
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
0
Comment
Question by:sammySeltzer
  • 4
  • 4
8 Comments
 
LVL 23

Accepted Solution

by:
nemws1 earned 500 total points
ID: 39256468
What are you calling this stored procedure from and is *it* doing the error capturing properly?  I usually would *NOT* use RAISERROR like this (I use it for system level, not application level).

Usually, you would have 2 variables as a part of your procedure call that are OUTPUT vars:

ALTER  PROCEDURE [dbo].[sp_SignUp]
	@cosID int,
	@locid int,
	@dat int,
	@UserName varchar(50),
        @returnCode INT OUTPUT,
        @errorStr VARCHAR(100) OUTPUT
AS
....

Open in new window


You put your return code (1 for good, -1 for bad or whatever you decide) and *if* there is an error, you populate @errorStr.  You check both of these in your *client* and have that pop up a box to the user, or continue on, etc.
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 39256557
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?
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39256581
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?
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 28

Author Comment

by:sammySeltzer
ID: 39256616
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

0
 
LVL 23

Expert Comment

by:nemws1
ID: 39256684
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?
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 39256721
No, it didn't show up.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39256759
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

0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 39256964
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question