Monthly Archives: May 2015

Don’t drop that Stored Procedure, Alter it!

Database Administrator, dba, SQL Server, T-SQL, , , , , , , , , , , , , , ,

So when writing scripts to create tables you might often include some SQL like below to first assess whether or not the table exists before dropping it.

IF OBJECT_ID('Schema.TableName', 'U') IS NOT NULL
      DROP TABLE Schema.TableName
      GO

 

This might be because you are still testing data and want a table with a different structure to be created or to exist and be populated with different data while using the same table name. So it’s the right thing to do in that circumstance but you may be wrongly carrying that thinking forward into creating stored procedures and user defined functions.

By dropping an SP or UDF you are also breaking any securities or permissions associated with that SP or UDF meaning these permissions etc. will have to be created again.

But for SPs and UDFs you don’t actually need to drop the SP or UDF you just want to change it if it exists. In which case you use Alter rather than Create. However what if you are unaware as to whether the SP or UDF actually exists?

I propose creating dummy SP’s or UDF’s in their place which will simply be over written as demonstrated below.

-- ALTER STORED PROCEDURE
-- THIS DUMMY SP JUST SELECTS 1
IF OBJECT_ID('[Schema].[NameOfStoreProcedure]') IS NULL
	EXEC ('CREATE PROCEDURE [Schema].[NameOfStoreProcedure] AS SELECT 1')
GO

ALTER PROCEDURE [Schema].[NameOfStoreProcedure] @DateParameter DATE
	,@IntParameter INT
	,@CharParameter VARCHAR(30)
AS
BEGIN
	------------------------------------------------------
	------------------------------------------------------
	--INSERT STORED PROCEDURE LOGIC HERE
	--E.G.
	SELECT 1
	------------------------------------------------------
	------------------------------------------------------
	
END;
GO

-- ALTER FUNCTION
-- THIS DUMMY UDF SIMPLY SETS THE PARAMETER @INT TO 1
IF OBJECT_ID('[Schema].[NameOfFunction]') IS NULL
	EXEC ('
CREATE FUNCTION [Schema].[NameOfFunction] (@INT AS INT)
RETURNS INT
AS
BEGIN
	SELECT @INT = 1
	RETURN @INT
END
')
GO

ALTER FUNCTION [Schema].[NameOfFunction] (@INT AS INT)
RETURNS INT
AS
BEGIN
	SET @INT = @INT + 1
	RETURN @INT
END
GO

-- RUN FUNCTION TO SEE RESULT
SELECT [Schema].[NameOfFunction](10) AS ReturnedValue

 

Universal Error Logging for Stored Procedures in SQL Server

Reporting, SQL Server, T-SQL, , , , , , , ,

Probably the best way to log whether your stored procedure ran correctly or threw an error during its run is to create another stored procedure to capture the events of the run.

This example although basic captures the primary data you would want to keep a record of regarding a procedures run.

  • The name of the stored procedure
  • The start time of the stored procedure
  • The time the stored procedure finished/errored
  • A user comment/error message
  • An assigned Log ID

Below is a pre-baked example requiring just a change to the DatabaseName, and if you wish SP_Name, text fields, as instructed in the comments of the SQL below, to test run the script.

This will create a SP_Log table and the stored procedure InsertRunIntoSpLog which can be reused over and over again when making stored procedures for other jobs to log the success/failure of these jobs.

Step 3 is an example stored procedure, which can be renamed as something other than SP_Name, which simply displays the date time.  Once created whenever the following is executed, EXEC dbo.SP_Name, the current date time will be returned and a record of the procedures run will be added to a row in the SP_Log table.

Although as stated above this is a basic example it is a very powerful foundation which can be built upon for very comprehensive event and transaction logging.

 


— CHANGE [DatabaseName] TO THE NAME OF THE DB REQUIRED
— CHANGE SP_NAME TO THE NAME OF THE SP REQUIRED
—————————————————
— STEP 1
— CREATE TABLE TO STORE ERRORS
—————————————————
USE [DatabaseName]
GO
— DROP LOG TABLE IF ALREADY EXISTS
IF OBJECT_ID('dbo.SP_Log', 'U') IS NOT NULL
DROP TABLE dbo.SP_Log
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
— CREATE LOG TABLE
CREATE TABLE [dbo].[SP_Log] (
[LogID] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL
,[NameOfSp] [nvarchar](100) NOT NULL
,[StepCode] [varchar](1) NOT NULL
,[RunDate] [datetime] NOT NULL
,[Comment] [nvarchar](200) NULL
,PRIMARY KEY CLUSTERED ([LogID] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
—————————————————
— STEP 2
— CREATE STORED PROCEDURE TO WRITE ERRORS TO ERROR TABLE
—————————————————
USE [DatabaseName]
GO
IF OBJECT_ID('dbo.InsertRunIntoSpLog', 'p') IS NOT NULL
DROP PROCEDURE [dbo].[InsertRunIntoSpLog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— CREATE STORED PROCEDURE
CREATE PROCEDURE [dbo].[InsertRunIntoSpLog] @SpVariableName NVARCHAR(100)
,@StepCode VARCHAR(1)
,@Comment NVARCHAR(400)
AS
BEGIN
INSERT INTO SP_Log (
NameOfSp
,StepCode
,RunDate
,Comment
)
VALUES (
@SpVariableName
,@StepCode
,getutcdate()
,@Comment
)
END
GO
—————————————————
— STEP 3
— STORED PROCEDURE RUN / ERROR CATCH EXAMPLE
—————————————————
IF OBJECT_ID('dbo.SP_Name', 'p') IS NOT NULL
DROP PROCEDURE [dbo].[SP_Name]
GO
CREATE PROCEDURE [dbo].[SP_Name]
AS
BEGIN
BEGIN TRY
— INSERT SP RUN LOG FOR SP START
EXEC InsertRunIntoSpLog 'SP_NAME' — SP_NAME
,'S' — S, F OR E (START, FINNISH OR ERROR)
,'' — USER COMMENT
— QUERY HERE
— FOR EXAMPLE
SELECT GETDATE() AS CurrentDateTime
— INSERT SP RUN LOG FOR SP END
EXEC InsertRunIntoSpLog 'SP_NAME'
,'F'
,''
END TRY
BEGIN CATCH
PRINT 'error!'
— INSERT SP RUN LOG FOR SP ERROR
DECLARE @error_number AS INTEGER
DECLARE @error_message AS VARCHAR(400)
SET @error_number = error_number()
SET @error_message = left(error_message(), 400)
PRINT 'error_message: ' + @error_message
EXEC InsertRunIntoSpLog 'SP_NAME'
,'E'
,@error_message
END CATCH
END

view raw

Universal Error Logging for Stored Procedures in SQL Server

hosted with ❤ by GitHub

Report Requirements Template

Reporting, SQL Server, T-SQL, , , , , , , , ,

When a developer is asked to create a business report it always involves a meeting of two disciplines.

The developer has the technical knowledge of pulling raw data, manipulating it and presenting interpreted data as information to be consumed by an end user. They may also know the location and source of the data to be used and how regularly this source is refreshed or updated with new data.

The Requester of the report will usually be the person with business domain knowledge. They understand the business rules which need to be applied to the data to transform it into information.

An obvious example for this is the relationship between a SQL developer working in an accounting environment. The developer is not an account and the account is not a developer but the two need to be able to collaborate and communicate to ensure that a report eventually outputs the correct information at the require period reliably.

Failure in achieving this goal can have disastrous consequences. For example projecting further revenue based on faulty data.

A good start for creating a foundation for effective communication and collaboration is for the developer and requester to step through a requirements template. This gives the report a structure and focus while also serving as a means of documenting the creation of the report from a business perspective and a future resource from a development perspective, i.e. the requirement of a future developer new to the report updating and modifying the report.

Attached is an example Template.

The Requirements sheet deals with questions like:

Who is the requester?

Why is the report needed?

What information will the report return?

This sheet will primarily be filled in by the requester.

The Report Fields sheet tries to plug the gap in knowledge between the requester and the developer by exploring what is the information required and what fields, calculations correspond to this information. The developer and requester may need to step through this sheet together to ensure their intended outcomes align.