December 1, 2013 at 9:40 am
I have the following stored procedure (shown at the bottom of this posting). It attempts to do a simple insertion of a new record into a table in my SQL Server 2012 database.
I'd like to adjust the stored procedure to where it returns a specific error code to indicate if the user has attempted to insert a new record that's actually a duplicate record and failed to insert.
I'd like the stored procedure to be able to return this error code to where I can identify what happened in a Windows Forms application in Visual Basic 2013 and can handle presenting a msg. to the user indicating that their record insertion attempt failed because it would have been a duplicate record.
Any suggestions for modifying this stored procedure (below) would be appreciated to accomplish what I'm desiring.
Thanks.
ALTER PROCEDURE [dbo].[sp_ins_recipients_of_scoring_reports]
(
@RecipientEmail varchar(50) ,
@ContestYear char(4) = NULL ,
@RecipientType varchar(50) = NULL ,
@RecipientName varchar(100) = NULL ,
@RecipientReportType varchar(50) = NULL
)
AS
INSERT INTO
[tblRecipientsOfScoringReports]
(
[RecipientEmail],
[ContestYear],
[RecipientType],
[RecipientName],
[RecipientReportType]
)
VALUES
(
@RecipientEmail,
@ContestYear,
@RecipientType,
@RecipientName,
@RecipientReportType
)
December 1, 2013 at 2:07 pm
One way to do what you want is to attempt the insert inside a TRY/CATCH block, and then use an OUTPUT parameter to return the error number to it when the stored procedure runs.
I added a primary key to your table to enforce a constraint. You may need to modify it to suit. Just add all the columns that determine uniqueness.
If you run the code that might fail inside a TRY/CATCH block, you can capture the error and assign it to a variable or output parameter, which you can then pass back to your .NET code. (I'm not a .NET guy, so I don't know if you can't access the Errors collection or whatever it is in .NET inside your code). If the value of @ErrorNo is 0 then the stored procedure ran successfully. If you wanted, you could check the value in your CATCH block and respond accordingly.
ALTER TABLE [tblRecipientsOfScoringReports]
ADD CONSTRAINT pkRecips PRIMARY KEY (RecipientEMail,ContestYear);
GO
ALTER PROCEDURE [dbo].[usp_ins_recipients_of_scoring_reports]
(
@RecipientEmail varchar(50) ,
@ContestYear char(4) = NULL ,
@RecipientType varchar(50) = NULL ,
@RecipientName varchar(100) = NULL ,
@RecipientReportType varchar(50) = NULL,
@ErrorNo INT OUTPUT
)
AS
BEGIN
BEGIN TRY
INSERT INTO
[tblRecipientsOfScoringReports]
(
[RecipientEmail],
[ContestYear],
[RecipientType],
[RecipientName],
[RecipientReportType]
)
VALUES
(
@RecipientEmail,
@ContestYear,
@RecipientType,
@RecipientName,
@RecipientReportType
)
END TRY
BEGIN CATCH
SET @ErrorNo = @@ERROR
PRINT @ErrorNo
END CATCH
END
-- test... run twice... first should succeed, second should fail.
TRUNCATE TABLE [tblRecipientsOfScoringReports];
GO
-- this will succeed (first attempt)
exec [dbo].[usp_ins_recipients_of_scoring_reports]
'me@here.com',
'2001',
@ErrorNo=0;
-- this will fail because I'm inserting the same record again (PK violation).
exec [dbo].[usp_ins_recipients_of_scoring_reports]
'me@here.com',
'2001',
@ErrorNo=0;
BEGIN TRY
-- RAISERROR with severity 11-19 will cause execution to
-- jump to the CATCH block.
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
Side note: Do not use "sp_" as a prefix for the stored procedures you write. "sp_" is reserved for system stored procedures. maybe use "usp_" instead. (Or look around here for Best Practices.)
December 1, 2013 at 5:35 pm
pietlinden (12/1/2013)
Side note: Do not use "sp_" as a prefix for the stored procedures you write. "sp_" is reserved for system stored procedures. maybe use "usp_" instead. (Or look around here for Best Practices.)
I'd like to emphasize this good note that pietlinden posted. NEVER use sp_ unless you intend to make the stored procedure live in the Master database AND you intend to mark it as a system object (and that brings on a wealth of other methods and precautions too lengthy to list here).
Shifting gears, I'd also like to suggest dropping the old habit of using Hungarian Notation, altogether. Except for some rarities, it just isn't necessary, causes extra typing, causes unnecessary screen clutter, and if you can't tell that something is a stored procedure just by the way it's used, then you're going to need a whole lot more than marking every one of them with "usp_". If I'm allowed to define a best practice, then I'd have to say that it's a worst practice to mark stored procedures with any prefix that's included for the sole purpose of identifying the code as a stored procedure.
You should also get into the healthy habit of always using the 2 part naming convention on all objects. Not doing so is computationally (as in "performance problem" for high hit-ratio procs) just as bad as naming procs with an "sp_" prefix.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2013 at 5:45 pm
Shifting gears again, VARCHAR(50) for eMail addresses simply isn't wide enough. I'd make that a VARCHAR(250) and still be prepared for the occasional truncation. Yes, I've seen it happen especially at only VARCHAR(50).
I'm also hoping that your recipient and report type columns are FKd to a lookup table for each. My temptation would be to use Integers or much shorter type codes for those two columns to be FKd to Integer/short type code columns in the lookup tables.
You'll also live to strongly regret using CHAR(4) for the contest year for multiple reasons. I'd at least make that a DATE datatype.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2013 at 8:06 pm
Brad,
Welcome to SSC... consider yourself schooled. (I'm still there, and I've been here for a while!)
Looks like you're a recovering Access/VB(A) programmer. Definitely true what Jeff said. Hungarian notation is kinda nuts. If you want to know what a variable's datatype is, just look at the declaration at the top of the stored procedure...
SQL Server takes a bit of getting used to, especially the way it returns errors (in contrast to the way Access does it!).
Hang in there. Key thing here is to ask an answerable question - and you'll learn a lot!
December 1, 2013 at 8:28 pm
I'll also say "congrats" on the very readable code you posted. It's nice to see some thoughtful casing.
To add to what pietliden posted for code, you might want to add the following to any and all stored procedures to prevent the application from interpreting a rowcount message as a return or an error.
SET NOCOUNT ON;
That would be one of the first things to include after the "AS"
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2013 at 9:39 pm
Thanks for the observations and recommendations.
The stored procedure snippet I posted is from an old VB6 application I developed many years ago. I'm in the process of rewriting the front-end in .NET and basically overhauling the entire thing.
The original collection of stored procedures was written using the "sp" prefix many years ago and I have yet to get around to changing those to more appropriate names instead of using the "sp" prefix. This is a pro bono application I developed many years ago, so I must admit that I was very loose in my development of the app. / database / etc. since I wasn't getting paid anything for it.
Thanks again.
December 1, 2013 at 11:06 pm
So are you all set now? Do you understand pietlinden's good code examples?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2013 at 9:28 am
All is good. Thanks again for the collective direction on my question.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply