Solved

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

Posted on 2013-06-18
8
202 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now