Error Trapping (TRY CATCH) in SQL Server Stored Procedures

Here is a way to add some simple but effective error trapping into your stored procedures:

Full code below.

Points to note:

1. First line of the SP we have a BEGIN TRY

2. At the end of the SP we have the END TRY

3. In the CATCH we grab the error info and put it into a string being careful with the nulls.

4. We then add that error info to our ErrorLog (this is the ErrorLog structure on TheDatabase)

The result is:

Which gives us some pretty useful information as to what has gone wrong.

What do you think?  Got any suggestions?

Hope that helps, 
Jon Bosker

DB Gurus, Australia

CREATE PROCEDURE TestSP

AS

BEGIN TRY

SET NOCOUNT ON;

-- All the other commands go here

SELECT 1/0;

END TRY

BEGIN CATCH

DECLARE @ErrorTrack varchar(MAX) =

 'ErrorNumber: '       +  ISNULL(CAST(ERROR_NUMBER() AS varchar), '0') + 

     '. ErrorSeverity: '   + ISNULL(CAST(ERROR_SEVERITY() AS varchar), '0') + 

     '. ErrorState: '      + ISNULL(CAST(ERROR_STATE() AS varchar), '0') + 

     '. ErrorLine:'        + ISNULL(CAST(ERROR_LINE() AS varchar), '0')  

 INSERT INTO [ErrorLog](Module, ErrorMessage, ErrorTrack, ErrorTime, [Path]) 

VALUES (ISNULL(ERROR_PROCEDURE(),''), ERROR_MESSAGE(), @ErrorTrack, GETDATE(), 'Stored Procedure on ' + DB_NAME())

END CATCH

GO

---------------------------------------------------------

-- Test the results:

EXEC TestSP

SELECT * FROM [ErrorLog] ORDER BY ErrorLogID DESC

-- Cleanup:

DROP PROCEDURE TestSP

Here is the structure of our ErrorLog but you can adapt the code above to use any error log:

CREATE TABLE [dbo].[ErrorLog](

[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,

[Module] [nvarchar](50) NOT NULL,

[ErrorMessage] [nvarchar](max) NOT NULL,

[ErrorTrack] [nvarchar](max) NOT NULL,

[ErrorTime] [datetime] NOT NULL,

[Path] [nvarchar](1000) NULL,

 CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED 

(

[ErrorLogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Grant Rights To All Routines (STORED PROCEDURES AND FUNCTIONS)

It is seldom a good idea to grant dbo rights to your users so here is an easy way to control who can run stored procedures and functions and who can view them.

Essentially the steps are:

1. Create new database roles called sp_runner and sp_viewer.. these are essentially hollow roles as they cannot do anything at this point.
2. Grant the role sp_runner the right to run, and sp_view the right to view (but not change) the stored procedures on the current database
3. Through SSMS or a script you can then assign those roles to database users.

The stored procedure below handles steps 1 and 2 and all you need to do is:
exec spGrantRightsToAllRoutines

We use this to allow:

• Our web app to run stored procedures. We like to grant the account that runs the web app the minimal rights to do its job.
• Our developers to view stored procedures on non-development databases. We like to have tight control over the change process but it is helpful for them to see the routines.

Hope that helps
Jon Bosker
www.dbgurus.com.au
-----------------------------------------------------------------------------------------
-- Refresh or create the SP spGrantRightsToAllRoutines:
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spGrantRightsToAllRoutines')
DROP PROCEDURE spGrantRightsToAllRoutines
GO

CREATE PROCEDURE [dbo].[spGrantRightsToAllRoutines]
AS
/*
Stored procedure to create 2 new database roles sp_viewer and sp_runner
and grant rights rights to those roles. After that all you need to do is
give those roles to the database users who need them

exec spGrantRightsToAllRoutines
*/

BEGIN
IF DATABASE_PRINCIPAL_ID('sp_runner') IS NULL
CREATE ROLE [sp_runner]
IF DATABASE_PRINCIPAL_ID('sp_viewer') IS NULL
CREATE ROLE [sp_viewer]

DECLARE @t TABLE (Name varchar(8000), id int identity(1,1) )

INSERT INTO @t
SELECT SPECIFIC_SCHEMA  + '.[' + ROUTINE_NAME + ']' FROM INFORMATION_SCHEMA.ROUTINES
WHERE NOT (ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE = 'TABLE') -- this one causes an error

DECLARE @sSQL varchar(8000), @counter int
SET @counter = 1

WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL='
GRANT EXEC ON ' + [Name] + ' TO sp_runner;
GRANT VIEW DEFINITION ON ' + [Name] + ' TO sp_viewer;'
 FROM @t WHERE id = @counter
PRINT @sSQL
  EXEC (@sSQL)  -- comment out this line to test it

SET @counter = @counter + 1
END
END

GO

exec spGrantRightsToAllRoutines

(CLEANUP) DROP ALL VIEWS FROM DATABASE

Useful little code snippet that removes all views from your database - we are getting the list of view from the database using this: SELECT * FROM INFORMATION_SCHEMA.VIEWS

Enjoy, Jon
DB Gurus
11 March 2016
------------------------------------------------------------------------------------
-- BACKUP YOUR DATABASE BEFORE YOU RUN THIS --
-- Create a temp table variable to store all the view names we are going to DROP

DECLARE @t TABLE
(
FullViewName varchar(8000),
id int identity(1,1)
)


-- Add them
INSERT INTO @t
SELECT TABLE_SCHEMA + '.[' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.VIEWS -- you can add drop criteria here WHERE...

-- List those that are going to be dropped
SELECT * FROM @t

DECLARE @sSQL varchar(8000)
DECLARE @counter int
SET @counter = 1

-- Build and run the SQL statements to dropd the
WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL = 'DROP VIEW ' + FullViewName  FROM @t WHERE id = @counter
PRINT @sSQL
EXEC (@sSQL)
SET @counter = @counter + 1
END

List the connections to a database (sp_who saved into a table)

Cool little stored procedure that you can install anywhere but I have installed on the Temp database and it tells you who is connected to a database.

It uses sp_who but puts the results into a database that you can query but really all we ever want to know is who is accessing a particular database and sp_who has always been restrictive in that sense.

Easy to call:

EXEC Temp.dbo.ListConnections @sDatabaseName = 'MyDatabaseName'
 
Hope that helps, Jon Bosker, DB Gurus, 15 Dec 2015



CREATE PROCEDURE dbo.ListConnections
(
    @sDatabaseName varchar(MAX)
)
AS

-- Create a table to store the results:
DECLARE @Who TABLE (
          [spid]        int
        , [ecid]        int
        , [status]    varchar(50)
        , [loginame]    varchar(255)
        , [hostname]    varchar(255)
        , [blk]        varchar(50)
        , [dbname]    varchar(255)
        , [cmd]        varchar(255)
        , [request_id] int
        )

-- Put the results of sp_who in there:
INSERT INTO @Who EXEC sp_who 



-- Query that table to get the connections to the specified database
SELECT * FROM @Who WHERE dbname = @sDatabaseName
GO

Script to index all foreign key columns for

Depending on your database design and number of rows in the table a possibly good performance improvement is to index all columns that refer to a primary key so that both ends of the relationship are indexed.  This script takes the grunt work out of it by using the database schema to find those columns and then index them.

I have tried to comment it to explain how it works. If anyone know where the monster SQL statement comes from please let me know and I will credit them accordingly.

Hope that helps, Jon Bosker, DB Gurus, 26 Nov 2015



--------------------------------------------------------------
-- Script to create indexes on all columns that have a foreign key constraint
-- note that this only works for single column foreign keys (all we use)
--------------------------------------------------------------
-- create a table variable to put the SQL commands for subsequent running:
DECLARE @t TABLE
(
SQLCommand varchar(8000),
id int identity(1,1)
)

-- This monster select gets a list of all columns that have a foreign key
-- (sorry I cannot remember where I got it from originally) 
INSERT INTO @t (SQLCommand)
SELECT DISTINCT
'CREATE NONCLUSTERED INDEX [IX_' + CU.COLUMN_NAME + '] ON [' + FK.TABLE_NAME + '] (['+CU.COLUMN_NAME+'] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]' AS [IndexCommand]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU  ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
  ) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
LEFT JOIN (
SELECT I.*
FROM sys.indexes I
JOIN sys.objects O ON I.object_id = O.object_id 
) INX 
ON INX.name LIKE '%' + PK.TABLE_NAME + '%' and INX.type_desc = 'NONCLUSTERED' AND INX.name LIKE 'IX%'
WHERE INX.name IS NULL     


-- Now we just need to run it line by line
DECLARE @sSQL varchar(8000)
DECLARE @counter int
SET @counter = 1


WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL = SQLCommand  FROM @t WHERE id = @counter
PRINT @sSQL
EXEC (@sSQL)  -- comment out this line to test it
SET @counter = @counter + 1
END 

Script to delete all stored procedures or procedures with a certain name pattern

Useful script to delete all stored procedures or all procedures with a certain name pattern e.g. all that start with sp etc.  Developed for SQL Server 2008 R2  

2 parts: the first creates a table variable and puts in there the names of the SPs to delete from the information schema. Part 2 cycles through that table variable executing the DROP PROCEDURE command.

Hope that helps, Jon Bosker, DB Gurus, 25 Nov 2015



--------------------------------------------------------------
-- You can set a name pattern of stored procedures to delete:
-- e.g SET @sPattern = 'sp%' to just delete procedures that start with sp
DECLARE @sPattern varchar(MAX)
SET @sPattern = '%'  --<<-- all SPs

DECLARE @t TABLE
(
SPName varchar(8000),
id int identity(1,1)
)

INSERT INTO @t
SELECT SPECIFIC_SCHEMA  + '.[' + SPECIFIC_NAME + ']' FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME LIKE @sPattern


-- SELECT * FROM @t

DECLARE @sSQL varchar(8000)
DECLARE @counter int
SET @counter = 1


WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL = 'DROP PROCEDURE ' + SPName  FROM @t WHERE id = @counter
PRINT @sSQL
EXEC (@sSQL)  -- comment out this line to test it first
SET @counter = @counter + 1
END 

Script to find out Disk Space Per Table

Useful script to find out how much disk space each table takes up by querying the system tables.

Hope that helps, Jon Bosker, DB Gurus, 23 Nov 2015


SELECT 
T.name AS [TableName],
SUM(p.rows) AS [RowCount],
((SUM(a.data_pages) * 8) / 1024) AS SpaceUsedMB
FROM sys.tables T
JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
JOIN sys.partitions P ON I.object_id = P.OBJECT_ID AND I.index_id = P.index_id
JOIN sys.allocation_units A ON p.partition_id = A.container_id
WHERE T.name NOT LIKE 'dt%' 
AND I.OBJECT_ID > 255 
AND I.index_id <= 1
GROUP BY T.NAME, I.object_id, I.index_id, I.name 
ORDER BY ((SUM(a.data_pages) * 8) / 1024) DESC