Recently I came across a post on http://www.StackOverflow.com that basically asked how to write a generic stored procedure to handle dynamic SQL. Honestly I found the idea interesting and coming up with a solution rather fun.
Another user, HABO, also provided an answer, which in several ways was better than mine, and politely pointed out that I was missing a bit when it came to avoiding SQL Injection. So after I provided my initial answer I went back and played with it some more, using one of his ideas for part of it, and came up with a fairly good (I think) stored procedure. I thought I would post the code and go over a few of the things I did. The full code is at the bottom of the post. I’ve commented and formatted the code to make it fairly readable so if you want to just skip down, feel free.
The stored procedure as defined has 7 parameters. The tablename & schema, two optional columns to be searched on and the associated search value(s), and the parameter that gave me (and continues to give me) the biggest headache, the list of columns to be returned. Each of these parameters has to be “dealt with” in some way to avoid SQL Injection. Once we are certain each is “clean” then it is safe to construct and execute the dynamic SQL.
Of the seven the easiest to deal with are the two search values. These can be handled by basic dynamic SQL and parameterizing them using sp_executeSQL. That’s one of the thing’s it’s there for after all.
Next come the tableName and tableSchema parameters. First the tableSchema is defaulted to dbo if it is blank (or NULL). Then I “un-QUOTENAME” the variables for common delimiters. []s, “s, and ‘s. I did this by checking the first and last characters and if they are the same and one of the characters I’m looking for then I remove them and use the REPLACE function to remove the appropriate double character that the QUOTENAME function will create. And last but not least query sys.all_objects to see if the schema.tablename combination exists.
This is probably a good place to point out that I’m using the sys.all_objects and sys.all_columns system views instead of the sys.objects and sys.columns system views because they include the system tables/views. The references could easily be switched to the more commonly used sys.objects and sys.columns system views if you don’t want to allow system tables/views to be queried.
The parameters containing the columns to be searched are then handled almost exactly the same as tableName and tableSchema. “Un-QUOTENAME”, then search the sys.all_columns and sys.all_objects system views to make sure that each column exists within the specified table.
Last and absolutely hardest is the columnList parameter. First I use a split function (thanks again HABO) to split the values into a temp table. Then look for any popular references to *. tableName.*, schemaName.tableName.* etc and normalize them by changing them all to [tableSchema].[tableName].*. Next “un-QUOTENAME”, and check the system views to make sure that the columns all exist in the specified table. Last but not least re-quotename and recombine into a comma delimited list. This is unfortunately rather limited (no alias’ for example) but currently the best I’ve been able to manage while still doing my best to avoid SQL injection.
Last but not least the very easiest part for me, construct and execute the dynamic SQL.
Split Function
CREATE FUNCTION dbo.SplitCSL( @CSL AS nvarchar(4000) )
-- Based on Jeff Moden's design.
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
WITH Digits AS (
SELECT Digit
FROM ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS Digits( Digit )
),
Numbers AS (
SELECT Ten_3.Digit * 1000 + Ten_2.Digit * 100 + Ten_1.Digit * 10 + Ten_0.Digit + 1 AS Number
FROM Digits AS Ten_0
CROSS JOIN Digits AS Ten_1
CROSS JOIN Digits AS Ten_2
CROSS JOIN Digits as Ten_3 ),
cteTally(N) AS (
SELECT 0
UNION ALL
SELECT TOP ( DataLength( IsNull( @CSL, 1 ) ) )
Row_Number() OVER ( ORDER BY ( SELECT NULL ) ) FROM Numbers ),
cteStart(N1) AS (
SELECT N + 1
FROM cteTally
WHERE Substring( @CSL, N, 1 ) = N',' OR N = 0 )
SELECT Item = Substring( @CSL, N1, IsNull( NullIf( CharIndex( N',', @CSL, N1 ), 0 ) - N1, 8000 ) )
FROM cteStart;
GODynamic stored procedure
CREATE PROCEDURE [dbo].[usp_GenericDynamicSQL]
@columnList nvarchar(max) ='*',
@tableSchema sysname ,
@tableName sysname ,
@ColNameAsFilter1 nvarchar(255) ='',
@ColNameAsFilter2 nvarchar(255) ='',
@ColFilter1VAL nvarchar(max)='',
@ColFilter2VAL nvarchar(max)=''
AS
BEGIN
SET NOCOUNT ON;
--====================================================
-- Set default values
IF ISNULL(@tableSchema,'') = ''
SET @tableSchema = 'dbo'
ELSE
SET @tableSchema = LTRIM(RTRIM(@tableSchema))
IF ISNULL(@columnList,'') = ''
SET @columnList = '*'
SET @tableName = ISNULL(LTRIM(RTRIM(@tableName)),'')
SET @ColNameAsFilter1 = ISNULL(LTRIM(RTRIM(@ColNameAsFilter1)),'')
SET @ColNameAsFilter2 = ISNULL(LTRIM(RTRIM(@ColNameAsFilter2)),'')
SET @ColFilter1VAL = ISNULL(@ColFilter1VAL,'')
SET @ColFilter2VAL = ISNULL(@ColFilter2VAL,'')
--====================================================
-- Remove probably QUOTENAMEs from @tableSchema and @tableName before testing them
SET @tableSchema = CASE WHEN LEFT(@tableSchema,1) = '[' AND RIGHT(@tableSchema,1) = ']'
THEN SUBSTRING(REPLACE(@tableSchema,']]',']'),2,LEN(REPLACE(@tableSchema,']]',']'))-2)
WHEN LEFT(@tableSchema,1) = '"' AND RIGHT(@tableSchema,1) = '"'
THEN SUBSTRING(REPLACE(@tableSchema,'""','"'),2,LEN(REPLACE(@tableSchema,'""','"'))-2)
WHEN LEFT(@tableSchema,1) = '''' AND RIGHT(@tableSchema,1) = ''''
THEN SUBSTRING(REPLACE(@tableSchema,'''''',''''),2,LEN(REPLACE(@tableSchema,'''''',''''))-2)
ELSE @tableSchema END
SET @tableName = CASE WHEN LEFT(@tableName,1) = '[' AND RIGHT(@tableName,1) = ']'
THEN SUBSTRING(REPLACE(@tableName,']]',']'),2,LEN(REPLACE(@tableName,']]',']'))-2)
WHEN LEFT(@tableName,1) = '"' AND RIGHT(@tableName,1) = '"'
THEN SUBSTRING(REPLACE(@tableName,'""','"'),2,LEN(REPLACE(@tableName,'""','"'))-2)
WHEN LEFT(@tableName,1) = '''' AND RIGHT(@tableName,1) = ''''
THEN SUBSTRING(REPLACE(@tableName,'''''',''''),2,LEN(REPLACE(@tableName,'''''',''''))-2)
ELSE @tableName END
--====================================================
-- Test to make sure the schema.table exists
IF NOT EXISTS (
SELECT 1
FROM sys.all_objects
JOIN sys.schemas
ON sys.all_objects.schema_id = sys.schemas.schema_id
WHERE sys.all_objects.name = @tableName
AND sys.schemas.name = @tableSchema
AND sys.all_objects.[TYPE] IN ('S','U','V')
)
BEGIN
RAISERROR (N'Table %s.%s does not exist.',
16,
1,
@tableSchema,
@tableName)
RETURN
END
--====================================================
-- Test to make sure all of the comma delimited values
-- are valid columns for schema.table
-- Create and populate a list of columns from columnlist
DECLARE @ColumnListTable TABLE (Item varchar(255))
INSERT INTO @ColumnListTable
SELECT Item
FROM dbo.SplitCSL(@columnList)
-- Remove any extra spaces
UPDATE @ColumnListTable SET Item = LTRIM(RTRIM(Item))
-- "Fix" any * formats to a single format of [schema].[tablename].*
UPDATE @ColumnListTable SET Item = CASE WHEN Item IN (
'*',
@tableName + '.*', @tableName + '.[*]',
'[' + @tableName + '].*', '[' + @tableName + '].[*]',
@tableSchema + '.' + @tableName + '.*', @tableSchema + '.' + @tableName + '.[*]',
@tableSchema + '.' + '[' + @tableName + '].*', @tableSchema + '.' + '[' + @tableName + '].[*]',
'[' + @tableSchema + '].' + @tableName + '.*', '[' + @tableSchema + '].' + @tableName + '.[*]',
'[' + @tableSchema + '].' + '[' + @tableName + '].*', '[' + @tableSchema + '].' + '[' + @tableName + '].[*]'
)
THEN '[' + @tableSchema + '].' + '[' + @tableName + '].*'
WHEN Item IN ('*','[*]') THEN '*'
ELSE Item END
--====================================================
-- Remove probably QUOTENAMEs from columns in column list before testing them
UPDATE @ColumnListTable SET Item =
CASE WHEN LEFT(Item,1) = '[' AND RIGHT(Item,1) = ']'
THEN SUBSTRING(REPLACE(Item,']]',']'),2,LEN(REPLACE(Item,']]',']'))-2)
WHEN LEFT(Item,1) = '"' AND RIGHT(Item,1) = '"'
THEN SUBSTRING(REPLACE(Item,'""','"'),2,LEN(REPLACE(Item,'""','"'))-2)
WHEN LEFT(Item,1) = '''' AND RIGHT(Item,1) = ''''
THEN SUBSTRING(REPLACE(Item,'''''',''''),2,LEN(REPLACE(Item,'''''',''''))-2)
ELSE Item END
-- Check for invalid column names
DECLARE @ColumnListFailures AS varchar(max)
SET @ColumnListFailures = ''
SELECT @ColumnListFailures = STUFF((
SELECT ', ' + Item
FROM @ColumnListTable
WHERE Item NOT IN (SELECT name
FROM sys.all_columns
WHERE object_id = OBJECT_ID(@tableSchema+'.'+@tableName))
AND Item <> '[' + @tableSchema + '].' + '[' + @tableName + '].*'
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
,1,2, '')
IF LEN(@ColumnListFailures) > 0
BEGIN
RAISERROR (N'Table %s.%s does not have columns %s that are listed in the columnList parameter.',
16,
1,
@tableSchema,
@tableName,
@ColumnListFailures)
RETURN
END
-- QUOTENAME each of the column names and re-create @ColumnList
SELECT @ColumnList = STUFF((
SELECT ', ' + CASE WHEN Item = '[' + @tableSchema + '].' + '[' + @tableName + '].*' THEN Item
ELSE QUOTENAME(Item) END
FROM @ColumnListTable
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
,1,2, '')
--====================================================
-- Remove probably QUOTENAMEs from first and second column filters before testing them
SET @ColNameAsFilter1 = CASE WHEN LEFT(@ColNameAsFilter1,1) = '[' AND RIGHT(@ColNameAsFilter1,1) = ']'
THEN SUBSTRING(REPLACE(@ColNameAsFilter1,']]',']'),2,LEN(REPLACE(@ColNameAsFilter1,']]',']'))-2)
WHEN LEFT(@ColNameAsFilter1,1) = '"' AND RIGHT(@ColNameAsFilter1,1) = '"'
THEN SUBSTRING(REPLACE(@ColNameAsFilter1,'""','"'),2,LEN(REPLACE(@ColNameAsFilter1,'""','"'))-2)
WHEN LEFT(@ColNameAsFilter1,1) = '''' AND RIGHT(@ColNameAsFilter1,1) = ''''
THEN SUBSTRING(REPLACE(@ColNameAsFilter1,'''''',''''),2,LEN(REPLACE(@ColNameAsFilter1,'''''',''''))-2)
ELSE @ColNameAsFilter1 END
SET @ColNameAsFilter2 = CASE WHEN LEFT(@ColNameAsFilter2,1) = '[' AND RIGHT(@ColNameAsFilter2,1) = ']'
THEN SUBSTRING(REPLACE(@ColNameAsFilter2,']]',']'),2,LEN(REPLACE(@ColNameAsFilter2,']]',']'))-2)
WHEN LEFT(@ColNameAsFilter2,1) = '"' AND RIGHT(@ColNameAsFilter2,1) = '"'
THEN SUBSTRING(REPLACE(@ColNameAsFilter2,'""','"'),2,LEN(REPLACE(@ColNameAsFilter2,'""','"'))-2)
WHEN LEFT(@ColNameAsFilter2,1) = '''' AND RIGHT(@ColNameAsFilter2,1) = ''''
THEN SUBSTRING(REPLACE(@ColNameAsFilter2,'''''',''''),2,LEN(REPLACE(@ColNameAsFilter2,'''''',''''))-2)
ELSE @ColNameAsFilter2 END
--====================================================
-- Check that the first filter column name is valid
IF @ColNameAsFilter1 <> '' AND
NOT EXISTS (SELECT 1
FROM sys.all_columns
WHERE object_id = OBJECT_ID(@tableSchema+'.'+@tableName)
AND name = @ColNameAsFilter1)
BEGIN
RAISERROR (N'Table %s.%s does not have a column %s.',
16,
1,
@tableSchema,
@tableName,
@ColNameAsFilter1)
RETURN
END
--====================================================
-- Check that the second filter column name is valid
IF @ColNameAsFilter2 <> '' AND
NOT EXISTS (SELECT 1
FROM sys.all_columns
WHERE object_id = OBJECT_ID(@tableSchema+'.'+@tableName)
AND name = @ColNameAsFilter2)
BEGIN
RAISERROR (N'Table %s.%s does not have a column %s.',
16,
1,
@tableSchema,
@tableName,
@ColNameAsFilter2)
RETURN
END
--====================================================
-- Construct & execute the dynamic SQL
DECLARE @sqlCommand nvarchar(max)
SET @sqlCommand = 'SELECT ' + @columnList + CHAR(13) +
' FROM ' + QUOTENAME(@tableSchema) + '.'+ QUOTENAME(@tableName) + CHAR(13) +
' WHERE 1=1 '
IF @ColNameAsFilter1 != ''
SET @sqlCommand = @sqlCommand + CHAR(13) +
' AND ' + QUOTENAME(@ColNameAsFilter1) + ' = @ColFilter1VAL'
IF @ColNameAsFilter2 != ''
SET @sqlCommand = @sqlCommand + CHAR(13) +
' AND ' + QUOTENAME(@ColNameAsFilter2) + ' = @ColFilter2VAL'
EXECUTE sp_executesql @sqlCommand,
N'@ColFilter1VAL nvarchar(MAX), @ColFilter2VAL nvarchar(MAX)',
@ColFilter1VAL, @ColFilter2VAL
ENDFiled under: Dynamic SQL, Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, dynamic sql, language sql, microsoft sql server, system functions, T-SQL 
