Monthly Archives: July 2015

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

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.

Image showing an SSRS report parameter options

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.

Advertisements

Combine and then split tsql insert statement files into batches of one thousand.

Recently I was given the task of executing hundreds of prepared sql files some containing multi-row insert statements numbering in the tens of thousands.

So I encountered two problems with this:

  • MSSQL Multi-row insert statements  actually have some limits i.e. a maximum of 1000 rows can be inserted.
  • I didn’t know which files had more or less than a thousand insert statements.

Luckily all the new rows were going into the same table and I knew that each file had the same flat/unformatted tsql structure.

This meant I could write a batch script to combine the files and then split the tsql insert statements into batches of 1000.

The batch script below is a little long winded so here are the main sections.

  • Combine Files
  • Remove unwanted lines of text  i.e. blank lines or insert statement sections.
  • Ensure every parentheses is followed by a comma
  • Inject the “insert into table” statement and GO every 1000 lines.
  • Remove double quotes

This was a somewhat specific case but hopefully you’ll be able to pick the batch script below apart for yourself and get some use out of it. But if there’s one little snippet of code I’d like to draw your attention to it is this little gem.

TYPE *.sql > CombinedScript.sql

 

Type that into a text file and save it as combine.bat, place the file in the folder with all your sql scripts and the OS will combine them all for you as CombinedScript.sql.

Here’s the rest of the script followed by some sample data showing the shape and structure of the original sql files.

(I’d like to thank the hilite.me website for creating this awesome online utility for creating HTML highlighted code that can just be dropped into a blog, as shown below. No more gist for me!)

@ECHO OFF

::VARIABLES
SET FileToUse="CombinedScript.sql"
SET FileToDelete="Query.sql"
SET FirstLine="Insert into [LoadProfiles] (Profile,Type,ProfileDate,ProfileValue,Active,Created,CreatedBy) Values"
SET BatchSplit="GO "
SET TextLineToAdd=%BatchSplit%%FirstLine%
::SET "TextLineToAdd=%TextLineToAdd:"=%"
::Combine SQL FILES

ECHO Combining Files . . . 
ECHO Please Wait
ECHO.
TYPE *.sql > %FileToUse%
ECHO Files Combined
ECHO.

ECHO Removing unwanted lines of text . . . 
ECHO Please Wait
ECHO.
::REF 1
::REMOVE THE LINES WITH INSERT AND VALUES FROM FILE
findstr /v "Insert Values" %FileToUse% > Temp.sql
::REF 2
::REMOVE BLANK ROWS FROM Temp.sql
findstr /v /r /c:"^$" /c:"^\ *$" Temp.sql >> CleanedFile.sql
::DELETE Temp.sql
IF EXIST Temp.sql del /F Temp.sql
ECHO Lines Removed
ECHO.


::REF 3
::REPLACE ) WITH ), TO ENSURE EVERY PARENTHESES IS FOLLOWED BY A COMMA
ECHO Adding Comma to each Parentheses missing a Comma . . .
ECHO Please Wait
ECHO.
    setlocal enableextensions disabledelayedexpansion

    set "search=)"
    set "replace=),"

    set "textFile=CleanedFile.sql"

    for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
        set "line=%%i"
        setlocal enabledelayedexpansion
        set "line=!line:%search%=%replace%!"
        >>PreparedFile.sql echo(!line!
        endlocal
    )
::DELETE CleanedFile.sql
IF EXIST CleanedFile.sql del /F CleanedFile.sql
::REF 3
::REPLACE ),, WITH ), TO ENSURE EVERY PARENTHESES IS FOLLOWED BY ONLY ONE COMMA
	    setlocal enableextensions disabledelayedexpansion

    set "search=),,"
    set "replace=),"

    set "textFile=PreparedFile.sql"

    for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
        set "line=%%i"
        setlocal enabledelayedexpansion
        set "line=!line:%search%=%replace%!"
        >>CleanedFile.sql echo(!line!
        endlocal
    )
ECHO Commas Added
ECHO.

ECHO Creating Query file . . .
ECHO Please Wait
ECHO.
::DELETE PreparedFile.sql
IF EXIST PreparedFile.sql del /F PreparedFile.sql
 
::Try to delete the file only if it exists
IF EXIST %FileToDelete% del /F %FileToDelete%
 
::REM If the file wasn't deleted for some reason, stop and error
IF EXIST %FileToDelete% exit 1

TYPE NUL > %FileToDelete% 

::REF 4
SETLOCAL
SET count=0
SET injectevery=1000
FOR /f "delims=" %%Z IN ('type CleanedFile.sql^|findstr /n "^"') DO (
SET /a count+=1
SET line=%%Z
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO(!line:*:=! >> Query.sql
IF !count!==%injectevery% ECHO.%TextLineToAdd% >> Query.sql
ENDLOCAL
SET /a count=count %% %injectevery% 
) 

::REF 3
::REPLACE " WITH NOTHING 
ECHO Adding Comma to each Parentheses missing a Comma . . .
ECHO Please Wait
ECHO.
    setlocal enableextensions disabledelayedexpansion

    set "search=""
    set "replace= "

    set "textFile=Query.sql"

    for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
        set "line=%%i"
        setlocal enabledelayedexpansion
        set "line=!line:%search%=%replace%!"
        >>Script.sql echo(!line!
        endlocal
    )
::DELETE CleanedFile.sql
IF EXIST CleanedFile.sql del /F CleanedFile.sql

PAUSE

::REF 1: http://stackoverflow.com/questions/418916/delete-certain-lines-in-a-txt-file-via-a-batch-file
::CREDIT GOES TO: http://stackoverflow.com/users/14138/rick

::REF 2: http://www.computing.net/answers/programming/delete-blank-line-from-a-txt-file-using-batch/25575.html
::CREDIT GOES TO: http://www.computing.net/userinfo/150780

::REF 3: http://stackoverflow.com/questions/23075953/batch-script-to-find-and-replace-a-string-in-text-file-without-creating-an-extra
::CREDIT GOES TO: http://stackoverflow.com/users/2861476/mc-nd

::REF 4: FOR INSERT NEW TEXT LINE: http://stackoverflow.com/questions/15859128/looking-for-batch-file-to-insert-new-lines-into-text-file
::CREDIT GOES TO: http://stackoverflow.com/users/2128947/magoo

/*CHANGE DATABASE_NAME*/

USE [DATABASE_NAME]
GO

/****** Object:  Table [dbo].[LoadProfiles]    Script Date: 06/12/2015 15:34:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[LoadProfiles](
	[ProfileID] [int] IDENTITY(1,1) NOT NULL,
	[Profile] [int] NULL,
	[Type] [varchar](8) NULL,
	[ProfileDate] [date] NULL,
	[ProfileValue] [decimal](12, 10) NULL,
	[Active] [bit] NULL,
	[Created] [datetime] NULL,
	[CreatedBy] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[LoadProfileID] 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

 
Insert into [dbo].[LoadProfiles] (Profile,Type,ProfileDate,ProfileValue,Active,Created,CreatedBy)
 Values
(1,'24H','2012/01/01',0.003348,1,getutcdate(),333),
(1,'24H','2012/01/02',0.003392,1,getutcdate(),333),
(1,'24H','2012/01/03',0.003278,1,getutcdate(),333),
(1,'24H','2012/01/04',0.003252,1,getutcdate(),333),
(1,'24H','2012/01/05',0.003203,1,getutcdate(),333)