Dynamic SQL FAQ 1: Convert conditional dynamic SQL string to stored proc

A lot of programmers coded the SQL query in dynamic SQL either in presentation layer or inside stored procedure, it has created a lot of problems in performance, security, maintenanbility, network and memory. More in The Curse and Blessings of Dynamic SQL.

From today onwards, I will compose a FAQ how to convert dynamic SQL to proper stored proc.

Dynamic SQL FAQ 1: Convert conditional dynamic SQL string to stored proc

The first FAQ I always heard is "I have a query based on a parameter, if the parameter is ALL, there will be no filter, if the parameter is a comma delimeter string (CSV), the query result will be filtered by the multiple values string in CSV format.

It has 2 challenges here:-
1) How to split the comma delimeter string
2) How to filter the result when it is not 'All'

It can be achieved easily by using dynamic SQL, the sample below is using SQL Server Northwind database with querying from SQL Query Analyser:-

declare @strSQL varchar(1000), @param varchar(100)
set @param = 'All'
set @param = '1,2,3'
set @strSQL = 'select * from employees'
if @param <> 'All'
set @strSQL = @strSQL + ' where employeeid in (' + @param + ')'
exec(@strSQL)

Let's see the 1st challenge, to split the CSV string, we can use the split function provided from Microsoft SQL Server Magazine Treat Yourself to Fn_Split():
This UDF lets you emulate VB's Split() function and parameter arrays

Then you can split the string by using the query easily:-

SELECT value FROM dbo.fn_split(@s, ',')

To filter either 'All' or not 'All', we have to use the power of "OR" operator, then the above dynamic SQL can be converted to proper SQL query as below:-

declare @s varchar(100)

set @s = '1,2,3'
--set @s = 'All'

select * from employees
where (@s <> 'All' AND employeeid in (SELECT value FROM dbo.fn_split(@s, ',')))
OR (@s = 'All')

Let's give me FIVE!