• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

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
0
sammySeltzer
Asked:
sammySeltzer
  • 4
  • 4
1 Solution
 
nemws1Database AdministratorCommented:
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
 
sammySeltzerAuthor Commented:
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
 
nemws1Database AdministratorCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
sammySeltzerAuthor Commented:
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
 
nemws1Database AdministratorCommented:
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
 
sammySeltzerAuthor Commented:
No, it didn't show up.
0
 
nemws1Database AdministratorCommented:
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
 
sammySeltzerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now