?
Solved

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

Posted on 2013-06-18
8
Medium Priority
?
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 23

Accepted Solution

by:
nemws1 earned 1500 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 29

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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 29

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 29

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 29

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

801 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