Tag Archives: stored procedure

How to get MS SQL Server job history using a stored procedure

April 20, 2017Database Administrator, dba, Development, Reporting, sql, SQL Server, SSRS, T-SQL, tsqlbypass, bypass sql server management studio, example, from, GET, guide, history, How To, howto, Job, lesson, sample, SP, SQL Server, sql server management studio, ssms, stored procedure, tutorialTidbytez 1

In a previous article (see article) I provided simple queries to return the server’s job history. It’s only natural that when you find yourself running the same script over and over again eventually you’ll get around to turning it into a stored procedure.

Well today was the day.

You can use the script below to create the Stored Procedure usp_GetJobHistory.

To deploy the SP to your instance you’ll need to replace the text PLACEHOLDER with the name of the database you’ll be deploying to. Ideally you should have a utility or administration database rather than deploying to Master.

The SP has 5 parameters.

Parameter	Datatype	Options	Definition
@DaysToPull	INT	Any Number	How many days of history you want working back from today
@Status	VARCHAR	One of the following: All’, ‘Succeeded’, ‘Failed’, ‘Retried’, ‘Cancelled’, NULL	Will limited the rows returned based on the status of the entry
@SortOrder	VARCHAR	ASC or DESC	The occurrence order i.e. oldest to newest
@JobName	VARCHAR	Any Text	Used in a LIKE operator to find jobs containing that text
@ReturnSql	BIT	0, 1 or NULL	Returns the SQL you can modify or run to return the history

Example command:

Create the SQL code to return the job history for jobs containing the work backup that failed in the past day sorted by newest to oldest.

EXEC dbo.GetJobHistory @DaysToPull = 1
,@SortOrder = ‘DESC’
,@Status = ‘Failed’
,@JobName = ‘Backup’
,@ReturnSql = 1

Tip: you could use this to create a SSRS report that could be published daily to notify stakeholders of any job failures.

-- REPLACE PLACEHOLDER
USE [PLACEHOLDER];
GO

IF OBJECT_ID('[usp_GetJobHistory]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[usp_GetJobHistory] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[usp_GetJobHistory] @DaysToPull INT
	,@Status VARCHAR(9) = NULL
	,@SortOrder VARCHAR(4) = NULL
	,@JobName VARCHAR(256) = NULL
	,@ReturnSql BIT = NULL
AS
BEGIN
	-- =============================================
	-- Author:      Bloggins
	-- Create date: 20170420
	-- Description: <Query to retrieve job history bypassing SSMS inbuilt viewer>
	-- Website: https://techtidbytes.wordpress.com/
	-- =============================================
	SET NOCOUNT ON

	BEGIN TRY
		-- Variable Declarations 
		DECLARE @PreviousDate DATETIME
		DECLARE @Year VARCHAR(4)
		DECLARE @Month VARCHAR(2)
		DECLARE @MonthPre VARCHAR(2)
		DECLARE @Day VARCHAR(2)
		DECLARE @DayPre VARCHAR(2)
		DECLARE @FinalDate INT
		DECLARE @StatusClause AS VARCHAR(255)
		DECLARE @Sql AS VARCHAR(MAX)

		IF @SortOrder IS NULL
			OR (
				@SortOrder <> 'ASC'
				AND @SortOrder <> 'DESC'
				)
		BEGIN
			SET @SortOrder = 'ASC'
		END

		IF @Status = 'All'
			OR @Status IS NULL
			OR (
				@Status <> 'All'
				AND @Status <> 'Failed'
				AND @Status <> 'Succeeded'
				AND @Status <> 'Retried'
				AND @Status <> 'Cancelled'
				)
		BEGIN
			SET @StatusClause = '0, 1, 2, 3'
		END
		ELSE IF @Status = 'Failed'
		BEGIN
			SET @StatusClause = '0'
		END
		ELSE IF @Status = 'Succeeded'
		BEGIN
			SET @StatusClause = '1'
		END
		ELSE IF @Status = 'Retried'
		BEGIN
			SET @StatusClause = '2'
		END
		ELSE IF @Status = 'Cancelled'
		BEGIN
			SET @StatusClause = '3'
		END

		SET @PreviousDate = DATEADD(dd, - @DaysToPull, GETDATE())
		SET @Year = DATEPART(yyyy, @PreviousDate)

		SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

		SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)

		SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

		SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)

		SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
		/*RunDuration FORMAT (DD:HH:MM:SS)*/
		SET @Sql = '
SELECT ROW_NUMBER() OVER(ORDER BY h.instance_id ' + @SortOrder + ' ) AS Row
	,h.Server AS ServerName
	,s.database_name AS DbName
	,j.name AS JobName
	,s.step_name AS StepName
	,h.step_id AS StepId
	,CASE 
		WHEN h.run_status = 0
			THEN ''Failed''
		WHEN h.run_status = 1
			THEN ''Succeeded''
		WHEN h.run_status = 2
			THEN ''Retried''
		WHEN h.run_status = 3
			THEN ''Cancelled''
		END AS RunStatus
	,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS RunTime
	,STUFF(STUFF(STUFF(RIGHT(REPLICATE(''0'', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, '':''), 6, 0, '':''), 9, 0, '':'') AS RunDuration
	,h.sql_severity AS SqlSeverity
	,h.message AS MessageReturned
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
	AND h.step_id = s.step_id
WHERE h.run_status IN (' + @StatusClause + ')
	AND h.run_date > ' + CONVERT(VARCHAR(255), @FinalDate)

		IF @JobName IS NOT NULL
		BEGIN
			SET @Sql = @Sql + '
AND j.name LIKE ' + '''' + '%' + @JobName + '%' + '''' + ''
		END

		SET @Sql = @Sql + '
ORDER BY h.instance_id ' + @SortOrder

		IF @ReturnSql = 1
		BEGIN
			PRINT (@Sql)
		END
		ELSE
		BEGIN
			EXEC (@Sql)
		END
	END TRY

	BEGIN CATCH
		PRINT '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
	END CATCH
END

How to use SQL to remove non-numeric characters from a field

June 18, 2016Coding, sql, SQL Server, T-SQLcharacters, cleaning with sql, function to remove letters, non-numeric, only numbers, remove, sql non-numeric characters, sql remove characters, stored procedure, stored procedure to remove letters, uncategorised, Using SQL to remove non-numeric characters from a fieldTidbytez 1

So I was working with phone numbers recently and the field was filthy. I mean there was absolutely no data entry validation whatsoever. Everything has been entered into this field from email addresses entered accidentally to phone numbers with little notes like (This is Steve’s number) to just random characters. There are millions of legitimate numbers in this field so in order to make the field workable the junk has to be taken out first.

A colleague of mine was kind enough to share this script with me for replacing characters. I’ve found it to be very beneficial as it is much more efficient than the solution I had been using of replacing all junk characters in a column row by row. This approach is set based replacing a single unwanted character from an entire column at a time.

CREATE TABLE #temp ([FreeText] VARCHAR(50) NOT NULL)

INSERT #temp
VALUES ('Hi There!!! 222  10 - 3476')

INSERT #temp
VALUES ('p@yahoo.com $%*


amp; 1234567 $%^&^')
DECLARE @DisallowedCharsASCIICodeMin TINYINT

,@DisallowedCharsASCIICodeMax TINYINT

,@ASCIICode TINYINT

,@Char CHAR(1)

,@ReplaceChar CHAR(1)

DECLARE @DisallowedChars TABLE (

[ASCIICode] TINYINT NOT NULL

,[Char] CHAR(1) NOT NULL

,[ReplaceChar] CHAR(1) NULL

,[Replaced] BIT NOT NULL DEFAULT 0

,PRIMARY KEY ([ASCIICode])

)
SELECT @DisallowedCharsASCIICodeMin = 32

,@DisallowedCharsASCIICodeMax = 126
SET @ASCIICode = @DisallowedCharsASCIICodeMin
WHILE @ASCIICode <= @DisallowedCharsASCIICodeMax

BEGIN

INSERT @DisallowedChars (

[ASCIICode]

,[Char]

)

VALUES (

@ASCIICode

,CHAR(@ASCIICode)

)
SET @ASCIICode = @ASCIICode + 1

END
DELETE @DisallowedChars

WHERE [Char] IN (

'0'

,'1'

,'2'

,'3'

,'4'

,'5'

,'6'

,'7'

,'8'

,'9'

)
WHILE EXISTS (

SELECT 1

FROM @DisallowedChars

WHERE [Replaced] = 0

)

BEGIN

SELECT TOP 1 @ASCIICode = [ASCIICode]

,@Char = [Char]

FROM @DisallowedChars

WHERE [Replaced] = 0
UPDATE #temp

SET [FreeText] = CASE

WHEN @ReplaceChar IS NULL

THEN REPLACE([FreeText], @Char, '')

ELSE REPLACE([FreeText], @Char, @ReplaceChar)

END

WHERE [FreeText] LIKE '%' + @Char + '%'
UPDATE @DisallowedChars

SET [Replaced] = 1

WHERE [ASCIICode] = @ASCIICode

END
SELECT *

FROM #temp
DROP TABLE #temp

GO

For my needs I’ve turned this logic into a stored procedure. I’ve also made a few changes. Firstly I’ve expanded the range of charters to exclude from unprintable characters to additional Unicode characters. I’ve also removed the option to add in a replace character as I only want non-numeric characters to be removed from the field. This allows me to also remove the case statement.

-- Drop stored procedure if it already exists
IF EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.ROUTINES
		WHERE SPECIFIC_SCHEMA = N'dbo'
			AND SPECIFIC_NAME = N'GetNumeric'
		)
	DROP PROCEDURE dbo.GetNumeric
GO
-- Create Procedure
CREATE PROCEDURE dbo.GetNumeric @TableName VARCHAR(255)
	,@ColumnName VARCHAR(255)
AS
SET NOCOUNT ON

DECLARE @sql AS VARCHAR(500)

CREATE TABLE #temp (
	DirtyColumn VARCHAR(255) NOT NULL
	,CleanColumn VARCHAR(255) NOT NULL
	)

--SELECT @TableName
SET @sql = 'SELECT ' + @ColumnName + ' AS DirtyColumn
,' + @ColumnName + ' AS CleanColumn FROM ' + @TableName

INSERT INTO #temp (
	DirtyColumn
	,CleanColumn
	)
EXEC (@sql)

DECLARE @DisallowedCharsASCIICodeMin TINYINT
	,@DisallowedCharsASCIICodeMax TINYINT
	,@ASCIICode TINYINT
	,@Char CHAR(1)
	,@ReplaceChar CHAR(1)
DECLARE @DisallowedChars TABLE (
	[ASCIICode] TINYINT NOT NULL
	,[Char] CHAR(1) NOT NULL
	,[ReplaceChar] CHAR(1) NULL
	,[Replaced] BIT NOT NULL DEFAULT 0
	,PRIMARY KEY ([ASCIICode])
	)

SELECT @DisallowedCharsASCIICodeMin = 1
	,@DisallowedCharsASCIICodeMax = 254

SET @ASCIICode = @DisallowedCharsASCIICodeMin

WHILE @ASCIICode <= @DisallowedCharsASCIICodeMax
BEGIN
	INSERT @DisallowedChars (
		[ASCIICode]
		,[Char]
		)
	VALUES (
		@ASCIICode
		,CHAR(@ASCIICode)
		)

	SET @ASCIICode = @ASCIICode + 1
END

DELETE @DisallowedChars
WHERE [Char] IN (
		'0'
		,'1'
		,'2'
		,'3'
		,'4'
		,'5'
		,'6'
		,'7'
		,'8'
		,'9'
		)

WHILE EXISTS (
		SELECT 1
		FROM @DisallowedChars
		WHERE [Replaced] = 0
		)
BEGIN
	SELECT TOP 1 @ASCIICode = [ASCIICode]
		,@Char = [Char]
	FROM @DisallowedChars
	WHERE [Replaced] = 0

	UPDATE #temp
	SET [CleanColumn] = REPLACE([CleanColumn], @Char, '')
	WHERE [CleanColumn] LIKE '%' + @Char + '%'

	UPDATE @DisallowedChars
	SET [Replaced] = 1
	WHERE [ASCIICode] = @ASCIICode
END

SET @sql = 'UPDATE T1
SET T1.' + @ColumnName + ' = T2.CleanColumn
FROM ' + @TableName + ' AS T1
INNER JOIN #temp AS T2
ON T1.' + @ColumnName + ' = T2.DirtyColumn
WHERE T1.' + @ColumnName + '= T2.DirtyColumn;'

EXEC (@sql)

DROP TABLE #temp
GO


GO

After deploying the SP above to your test environment you can test it with the following script.

IF OBJECT_ID('dbo.DirtyPhoneNumbers', 'U') IS NOT NULL
	DROP TABLE dbo.DirtyPhoneNumbers;

CREATE TABLE DirtyPhoneNumbers (PhoneNumbers VARCHAR(255));
GO

INSERT INTO DirtyPhoneNumbers (PhoneNumbers)
VALUES ('afef2313newfnaksdfn')
	,('afef2313n!!!!!!!!!!&dfn')
	,('afef====+++22221sdfn')
	,('afef!"£$%^&&7575757sdfn')

SELECT *
FROM DirtyPhoneNumbers

EXEC dbo.GetNumeric 'DirtyPhoneNumbers'
	,'PhoneNumbers'

SELECT *
FROM DirtyPhoneNumbers

How to write text to a file with SQL Server

August 25, 2015SQL Serverfile, How create text file in directory sql server, How To, how to write text to a file from SQL Server, How write text to file SSMS, howto, lesson, sp_OACreate, sp_OAMethod, sql server management studio, ssms, stored procedure, text, to, tutorial, write, Write text to File in SQL Server Management StudioTidbytez

The following is a tutorial on creating a stored procedure that will allow you to create a file in any directory and insert text into this newly created file.

In order for this to work you will need to authorise the running of system stored procedures with Object Linking and Embedding functionality (See OLE).

Authorisation is needed as the stored procedure we will create rely on the system SPs sp_OACreate and sp_OAMethod.

sp_OACreate: Creates an instance of an OLE object.

sp_OAMethod: Calls a method of an OLE object.

NOTE: By default, SQL Server blocks access to OLE Automation stored procedures by turning the components off as part of the security configuration for the server.

Run the script below to grant authorisation.

--AUTHORIZE SYSTEM STORED PROCEDURES
sp_configure 'show advanced options'
	,1;
GO

RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures'
	,1;
GO

RECONFIGURE;
GO

--AUTHORIZED

Next we will create the stored procedure WriteToFile. Substitute the DatabaseName with the database you will be using.

--CREATE STORED PROCEDURE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[WriteToFile]', 'P') IS NOT NULL
	DROP PROCEDURE [WriteToFile];
GO

CREATE PROCEDURE [dbo].[WriteToFile] @File VARCHAR(255)
	,@Text VARCHAR(MAX)
	WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @OLE INT
	DECLARE @FileID INT

	EXECUTE sp_OACreate 'Scripting.FileSystemObject'
		,@OLE OUT

	EXECUTE sp_OAMethod @OLE
		,'OpenTextFile'
		,@FileID OUT
		,@File
		,8
		,1

	EXECUTE sp_OAMethod @FileID
		,'WriteLine'
		,NULL
		,@Text

	EXECUTE sp_OADestroy @FileID

	EXECUTE sp_OADestroy @OLE
END;

Below is an example using the WriteToFile stored procedure.

--WRITE TO FILE EXAMPLE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'
SET @Txt = 'Hello World'

EXEC [DatabaseName].[dbo].[WriteToFile] @Path
	,@Txt;

Using the same stored procedure here’s an example writing a table to the file using concatenation and a loop. This process works by taking the table row by row and writing the concatenated value to the file. There are easier ways to achieve this however, i.e. utilising the export functionality built into SSMS and saving this as a package to be run as a job.

--STORED PROCEDURE CREATED
--WRITE TABLE TO FILE EXAMPLE
DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)
DECLARE @loopId AS INT
DECLARE @maxId AS INT
DECLARE @TempCustomer TABLE (
	ID_column INT IDENTITY PRIMARY KEY
	,FirstName NVARCHAR(100)
	);

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'

INSERT INTO @tempCustomer (FirstName)
VALUES ('Paul')
	,('Jim')
	,('John')

SET @loopId = 1
SET @maxId = (
		SELECT MAX(ID_column)
		FROM @TempCustomer
		)

WHILE @loopId <= @maxId
BEGIN
	SELECT @Txt = CONVERT(VARCHAR(10), ID_column) + ', ' + FirstName
	FROM @TempCustomer
	WHERE ID_column = @loopId

	PRINT @Txt

	EXEC [TEST_DB].[dbo].[WriteToFile] @Path
		,@Txt;

	SET @loopId = @loopId + 1
END

How to pass a multi-value parameter to a stored procedure from a SSRS Report

July 16, 2015Reporting, SQL Server, SSRS, T-SQLguide, How To, howto, lesson, Multi-value, multiple select, multivalue, parameter, Parameters, Passing a multi-value parameter to a stored procedure from a SSRS Report, report, Reports, SP, split, SSRS, stored procedure, tsql, tutorial, UDF, uncategorised, user defined functionTidbytez

When you allow for multiple field values to be selected in a SSRS report there needs to be additional logic added to the back end to deal with this.

This is best explained with an example scenario.

I have a table called Ireland with two columns, ID_Column and County. You can use the script below to create and populate this table. Run the query below to follow the working example.

CREATE DATABASE [TEST_DB];

USE [TEST_DB];
GO

/****** Object:  Table [dbo].[Ireland]    Script Date: 07/15/2015 10:49:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Ireland] (
	[ID_Column] [int] IDENTITY(1, 1) NOT NULL
	,[County] [varchar](9) NULL
	,PRIMARY KEY CLUSTERED ([ID_Column] 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

SET IDENTITY_INSERT [dbo].[Ireland] ON

INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (1, N'Antrim')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (2, N'Armagh')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (3, N'Carlow')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (4, N'Cavan')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (5, N'Clare')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (6, N'Cork')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (7, N'Derry')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (8, N'Donegal')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (9, N'Down')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (10, N'Dublin')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (11, N'Fermanagh')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (12, N'Galway')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (13, N'Kerry')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (14, N'Kildare')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (15, N'Kilkenny')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (16, N'Laois')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (17, N'Leitrim')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (18, N'Limerick')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (19, N'Longford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (20, N'Louth')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (21, N'Mayo')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (22, N'Meath')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (23, N'Monaghan')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (24, N'Offaly')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (25, N'Roscommon')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (26, N'Sligo')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (27, N'Tipperary')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (28, N'Tyrone')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (29, N'Waterford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (30, N'Westmeath')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (31, N'Wexford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (32, N'Wicklow')
SET IDENTITY_INSERT [dbo].[Ireland] OFF

If I wanted to allow a user to select every county from the Ireland table in an SSRS report I would create a stored procedure that simply executes the following query.

SELECT * FROM Ireland

However an SSRS report which allows users to choose counties in Ireland like below means that a WHERE clause needs to be introduced and be able to respond to the field values selected.

But the issue is that the SSRS report will pass the multi-valued parameter as a string with the values separated by a comma.

So instead of receiving the required: ‘Antrim’, ‘Armagh’, ‘Carlow’, ‘Cavan’ etc. for use in the WHERE clause.

SQL Server is passed: ‘Antrim, Armagh, Carlow, Cavan’ etc. which cannot be used.

So the first additional logic and code to be added to the back end to deal with the multi-value parameter is a User Defined Function (UDF) which splits the parameter. The following function and quotations are taken from the 4guysfromrolla website.

Function Scope:

“There are generally two parameters to a split function: the list to split and the character(s) to split on, the delimiter. In the following function we begin by declaring our input variables – @List, the list to split, and @SplitOn, the delimiter(s) to split on. The return value of this UDF is a table with two fields: Id, an identity column, and Value, an nvarchar(100) column.”

Function Logic:

“The main body of the function simply loops through the string finding the first occurrence of the delimiter on each pass. Once the delimiter has been found, the string is broken into two pieces. The first piece is inserted into the result table while the second piece replaces the original list. The loop continues until no more occurrences of the delimiter are found. Lastly, the remainder of the list is added to the result table. Return the table and you have a split function.”

Run the query below to follow the working example.

USE [TEST_DB];
GO

CREATE FUNCTION [dbo].[Split] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

(For a line by line break down of the function please see the webpage.)

Now that the split function exits within the database it is now possible to use a Stored Procedure to SELECT from the Ireland table pulling back specific counties by way of a WHERE clause.

Run the query below to follow the working example.

USE Test_DB;
GO

IF OBJECT_ID('[GetCounties]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[GetCounties] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[GetCounties] @County VARCHAR(MAX)
AS
	/*

NAME OF SP: GetCounties
Author:		Bloggins86
Date:		15/07/2015
Purpose:	Test multi-parameter select

*/
	------------------------------------------------------
	------------------------------------------------------
	--INSERT STORED PROCEDURE LOGIC HERE
	SELECT *
	FROM Ireland
	WHERE County IN (SELECT Value FROM dbo.Split(@County, ','))

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

Now that the populated table, split function and county select SP exists run the query below passing a string with multiple counties to return Dublin, Meath and Cork from the Ireland Table.

EXEC dbo.[GetCounties] 'Dublin, Meath, Cork'

You should now have returned the table with Dublin, Meath and Cork as separate row entries.

And that’s it, thanks for reading.

Don’t drop that Stored Procedure, Alter it!

May 22, 2015Database Administrator, dba, SQL Server, T-SQLAlter, drop sp without losing permissions, Dropping, effecting permissions, guide, how drop sp, how drop stored procedure, How To, howto, lesson, SP, SQL Server, stored procedure, tutorial, UDF, user defined functionTidbytez

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

May 20, 2015Reporting, SQL Server, T-SQLerror, logging, sql server, stored procedure, T-SQL, tsql, uncategorised, universal, Universal Error Logging for Stored Procedures in SQL ServerTidbytez

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

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: