Dynamic Sql Tricks

Dynamic SQL is easy enough, but is often susceptible to SQL Injection. One way around SQL Injection is to use parameters always. This is easier said than done. Here are some examples that show how this can be done. They get more complex with each example. This example shows how to use sp_executesql with parameters. create PROCEDURE Test3 @MinNumberOfGuests INT, @MaxNumberOfGuests INT AS DECLARE @Sql NVARCHAR(MAX) SET @Sql = 'select * from Site where MinNumberOfGuests = @MinNumberOfGuests and MaxNumberOfGuests = @MaxNumberOfGuests' EXEC sp_executesql @Sql, N'@MinNumberOfGuests INT, @MaxNumberOfGuests INT', @MinNumberOfGuests, @MaxNumberOfGuests GO Test3 25, 300 This shows how to handle parameters that are null which in this case signifies that the parameter is not to be in the where clause. The important thing to note is that "select top 0 " followed by all the parameters. This allows the for the value parameters of sp_executesql to always be included since there is no way to conditionally include them. It adds a very small amount of overhead to select variables, and top 0. create PROCEDURE Test5 @MinNumberOfGuests INT, @MaxNumberOfGuests INT AS DECLARE @Sql NVARCHAR(MAX) SET @Sql = 'select * from Site where MinNumberOfGuests = @MinNumberOfGuests and MaxNumberOfGuests = @MaxNumberOfGuests; select top mailto:0@MaxNumberOfGuests EXEC sp_executesql @Sql, N'@MinNumberOfGuests INT, @MaxNumberOfGuests INT', @MinNumberOfGuests, @MaxNumberOfGuests GO Test5 25, 300 This is a more robust implementation of the above. create PROCEDURE Test4 @MinNumberOfGuests INT = null, @MaxNumberOfGuests INT = null AS DECLARE @Sql NVARCHAR(MAX) DECLARE @AllSql NVARCHAR(MAX) SET @Sql = 'select * from Site WHERE 1 = 1 ' IF @MinNumberOfGuests is not null BEGIN Set @Sql = @Sql + ' AND MinNumberOfGuests = @MinNumberOfGuests' END IF @MaxNumberOfGuests is not null BEGIN Set @Sql = @Sql + ' AND MaxNumberOfGuests = @MaxNumberOfGuests' END Set @AllSql = @Sql + N'; Select top 0 @MinNumberOfGuests, @MaxNumberOfGuests' EXEC sp_executesql @AllSql, N'@MinNumberOfGuests INT, @MaxNumberOfGuests INT', @MinNumberOfGuests, @MaxNumberOfGuests GO Test4 25, null Test4 null, 300 Test4 25, 300 Test4 null, null