Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Generic Dynamic SQL Stored Procedure

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;
GO

Dynamic 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
	END

Filed 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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...