August 25, 2016 at 2:34 pm
Hello Gurus,
I have a table, tblEmployee, with 5 columns in it (EmployeeID, EmployeeName, EmployeeDeptID, EmployeeHireDate, SupervisorID). I have to write a stored procedure that includes error handling to output meaningful information when an exception is encountered. This simple stored procedure takes one input parameter (EmployeeID) and returns the data in the corresponding row. What would be the best way to write this procedure covering all aspects of error and exception handling that will add value to the application code that is going to call this stored procedure? I would appreciate your expert advice on this.
Code to generate my sample table is below..
if object_id ('tempdb..#tblEmployee') is not null
drop table #tblEmployee
create table #tblEmployee (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDeptID INT, EmployeeHireDate Date, SupervisorID INT)
insert #tblEmployee
values
(101, 'John Smith', 200, '2/21/1991', NULL),
(102, 'Jane Doe', 300, '4/10/1998', NULL),
(103, 'Kobi Bryant', 300, '8/17/2011', 102),
(104, 'Michael Phelps', 300, '3/12/2007', 102),
(105, 'Taylor Swift', 200, '5/11/2014', 101)
select * from #tblEmployee
- SQLCurious
August 25, 2016 at 2:43 pm
You want a try...catch block that either logs errors or returns a sensible message (or the original error message) back to the app. I'd say use THROW as well, but that's not in SQL 2005.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2016 at 3:10 pm
Thanks for your response, @gilamonster. I am using SQL 2014. Would you have any recommended sample that is standard and recommended?
I think I accidentally posted this in 2005 forum. Can one of the admins please move this to 2014?
- SQLCurious
August 25, 2016 at 3:54 pm
And what is this stored procedure expected to accomplish? A bit lean on the details.
August 25, 2016 at 4:02 pm
On a happy path, it should return the details of the row it finds a match for, just one row. A meaningful message, code for any exceptions/errors encountered.
-SQLCurious
August 25, 2016 at 4:08 pm
A couple of suggested Error messages:
--If the parameter is NULL return:
Parameter is not supplied. This stored procedure requires one parameter.
--If no data is found return:
Data not found based on supplied parameter. Parameter supplied is:
-SQLBill
August 25, 2016 at 9:28 pm
Here is a starting point with some basics included for you:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[schema_name].[proc_name]')
AND type IN (N'P', N'PC') )
DROP PROCEDURE schema_name.proc_name;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE schema_name.proc_name (@param NVARCHAR(500))
AS
BEGIN
/********************************************************************************
Created:
Purpose:
Author:
Called By:
Example:
Modification History:
Date Author Purpose
----------- --------------- ----------------------------------------------------
********************************************************************************/
SET NOCOUNT ON;
BEGIN TRY;
IF @param IS NULL
BEGIN
THROW 50000, 'Parameter @param not supplied.',1;
END;
SELECT some_column
FROM dbo.some_table
WHERE some_other_column = @param;
-- verify a row was found
IF @@ROWCOUNT = 0
RAISERROR ('Data not found based on supplied parameter. Parameter supplied is %s.',16,1,@param);
RETURN;
END TRY
BEGIN CATCH;
-- optionally log the error somewhere
THROW;
END CATCH;
END;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 26, 2016 at 9:35 am
Thanks a lot, Orlando!
- SQLCurious
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply