Use Dynamic SQL to Improve Query Performance & Avoiding Sql Injections, sp_depends

/*This article shows an example of a poor performing query and how to make it perform better using Dynamic SQL. It also demonstrates a way of coding dynamic SQL so it can easily be debugged. When we write dynamic SQL within a stored procedure not only do we have to give the user EXECUTE rights to the procedure but also specific access to the tables in the dynamic SQL.

*/

/*A sample of Poor performance query */

/*

It is a stored procedure that was taking a long time to execute. The purpose of the stored procedure is to return rows that match a given set of optional inputs. The way this is achieved using non-dynamic SQL is for the query to test the value of the parameter for NULL or a column equal to the value of the parameter. All columns on the table are defined to be NOT NULL and the passing in of a parameter with a NULL value logically means that we don't care what the value is. You might think that SQL Server would short-circuit the query when it sees that the parameter is null and not bother to look at the values on the table but it actually does a full table scan instead.

*/

USE [AdventureWorks]

GO

CREATE PROCEDURE SearchProducts

(

@Name nvarchar(50) = NULL,

@Color nvarchar(15) = NULL,

@ListPriceMin money = NULL,

@ListPriceMax money = NULL,

@ModifiedDateMin datetime = NULL,

@ModifiedDateMax datetime = NULL

)

AS

BEGIN

SELECT

[Name],

[Color],

[ListPrice],

[ModifiedDate]

FROM

[Production].[Product]

WHERE

([Name] LIKE @Name OR @Name IS NULL)

AND

([Colour] LIKE @Color OR @Color IS NULL)

AND

([ListPrice] >= @ListPriceMin OR @ListPriceMin IS NULL)

AND

([ListPrice] <= @ListPriceMax OR @ListPriceMax IS NULL) AND (ModifiedDate >= @ModifiedDateMin OR @ModifiedDateMin IS NULL)

AND

(ModifiedDate <= @ModifiedDateMax OR @ModifiedDateMax IS NULL) END GO --Well Performing but open to SQL Injection Dynamic SQL

USE [AdventureWorks]

GO

/**********************************************************************

-- Sample:

-- EXEC [SearchProducts_SQLInjectable] 'W%', 'SH%'

-- Injected SQL

-- EXEC [SearchProducts_SQLInjectable] @Name='W%''; SELECT * FROM HumanResources.EmployeeAddress --'

********************************************************************** /

CREATE PROCEDURE [dbo].[SearchProducts_SQLInjectable]

(

@Name nvarchar(62) = NULL,

@ProductNumber nvarchar(25) = NULL,

@ListPriceMin money = NULL,

@ListPriceMax money = NULL,

@ModifiedDateMin datetime = NULL,

@ModifiedDateMax datetime = NULL

)

AS

BEGIN

DECLARE @sSql nvarchar(4000)

DECLARE @sWhereClause nvarchar(4000)

DECLARE @NewLine nvarchar(4000)

SET @NewLine = CHAR(13) + CHAR(10)

SET @sWhereClause = ' ' -- Initialise

SET @sSql = 'SELECT [Name],' + @NewLine

+ ' [ProductNumber],' + @NewLine

+ ' [ListPrice],' + @NewLine

+ ' [ModifiedDate]' + @NewLine

+ ' FROM [Production].[Product]' + @NewLine

+ ' WHERE 1 = 1' -- Always true

IF @ProductNumber IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ProductNumber] LIKE ''' + @ProductNumber + ''''

IF @ListPriceMin IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] >= ' + Cast(@ListPriceMin as varchar(30))

IF @ListPriceMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] <= ' + Cast(@ListPriceMax as varchar(30)) IF @ModifiedDateMin IS NOT NULL SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate >= ' + Convert(varchar(30), @ModifiedDateMin, 121)

IF @ModifiedDateMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate <= ' + Convert(varchar(30), @ModifiedDateMax, 121) IF @Name IS NOT NULL SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE ''' + @Name + '''' SET @sSql = @sSql + @sWhereClause EXEC (@sSql) END GO

/*
Constructing SQL this way is not good, it leaves the procedure open to SQL Injection; little Bobby Tables might enter his name! Also, it does not allow the optimiser to reuse the execution plan. An example of how SQL Injection can be performed is given in the comments below:

*/

EXEC [SearchProducts_SQLInjectable] @Name='W%''; SELECT * FROM HumanResources.EmployeeAddress --'

/*
A slightly better way to construct the WHERE clause and provide a little more protection is to double up the quotes in each parameter by calling SQL Server's Replace function e.g.:

/*

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE ''' + Replace(@Name, '''', '''''') + ''''

/*

This above query is still not optimal as it still does not allow SQL Server to reuse the query when different values of @Name are passed in to the procedure, though it should prevent most forms of SQL injection.

*/

/* sp_depends is used to find out which procedures use which tables then this stored procedure will be listed

*/

EXEC sp_depends 'Production.Product'

/*The above lists out the all the stored procedure which uses this table*/

/*

Using Parameterised Dynamic SQL to avoid SQL Injection along with the debug option

*/

USE [AdventureWorks]

GO

-- **********************************************************************

-- Sample CALL:-- EXEC [SearchProducts_SQLNonInjectable] 'W%'

-- **********************************************************************

CREATE PROCEDURE [dbo].[SearchProducts_SQLNonInjectable]

(

@Name nvarchar(50) = NULL,

@ProductNumber nvarchar(25) = NULL,

@ListPriceMin money = NULL,

@ListPriceMax money = NULL,

@ModifiedDateMin datetime = NULL,

@ModifiedDateMax datetime = NULL,

@Debug bit = 0 -- When set to 1 just prints SQL and does not execute it

)

AS

BEGIN

DECLARE @sSql nvarchar(4000)

DECLARE @sWhereClause nvarchar(4000)

DECLARE @ParmDefinition nvarchar(4000)

DECLARE @NewLine nvarchar(4000)

SET @NewLine = CHAR(13) + CHAR(10)

SET @sWhereClause = ' ' -- Initialise

SET @sSql = 'SELECT [Name],' + @NewLine

+ ' [ProductNumber],' + @NewLine

+ ' [ListPrice],' + @NewLine

+ ' [ModifiedDate]' + @NewLine

+ ' FROM [Production].[Product]' + @NewLine

+ 'WHERE 1 = 1' -- Always true

IF @Name IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE @Name'

IF @ProductNumber IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ProductNumber] LIKE @ProductNumber'

IF @ListPriceMin IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] >= @ListPriceMin'

IF @ListPriceMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] <= @ListPriceMax' IF @ModifiedDateMin IS NOT NULL SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate >= @ModifiedDateMin'

IF @ModifiedDateMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate <= @ModifiedDateMax ' SET @ParmDefinition = '@Name nvarchar(50),' + @NewLine + ' @ProductNumber nvarchar(25),' + @NewLine + ' @ListPriceMin money,' + @NewLine + ' @ListPriceMax money,' + @NewLine + ' @ModifiedDateMin datetime,' + @NewLine + ' @ModifiedDateMax datetime' SET @sSql = @sSql + @sWhereClause IF @Debug = 0 EXEC sp_executesql @sSql, @ParmDefinition, @Name=@Name, @ProductNumber=@ProductNumber, @ListPriceMin=@ListPriceMin,

@ListPriceMax=@ListPriceMax,

@ModifiedDateMin=@ModifiedDateMin, @ModifiedDateMax=@ModifiedDateMax

ELSE

BEGIN

PRINT 'DECLARE ' + @ParmDefinition

PRINT ''

PRINT

'SET @Name = ' + IsNull('''' + @Name + '''','NULL')

PRINT 'SET @ProductNumber = ' + IsNull('''' + @ProductNumber + '''','NULL')

PRINT 'SET @ListPriceMin = ' + IsNull(Cast(@ListPriceMin AS varchar(30)),'NULL')

PRINT 'SET @ListPriceMax = ' + IsNull(Cast(@ListPriceMax AS varchar(30)),'NULL')

PRINT 'SET @ModifiedDateMin = ' + IsNull('''' + Convert(varchar(50), @ModifiedDateMin, 121) + '''','NULL')

PRINT 'SET @ModifiedDateMax = ' + IsNull('''' + Convert(varchar(50), @ModifiedDateMax, 121) + '''','NULL')

PRINT ''

PRINT @sSql

END

--END IF

//It is setted so as to get displayed in the sp_depends stored procedure.

-- Make sure this procedure is detected with sp_depends on table [Production].

--[Product]

IF 1 = 2 -- Always false

BEGIN

SELECT TOP(0) 0 FROM [Production].[Product]

END

--END IF

END

GO

Linking to the Previous Row

/*The client would like to see a report showing the Item name, the old price, the new price and the date range for which the new price was applied. This type of question has often given headaches to sql developers, as it usually involved messy subqueries using the max / min functions. Some DBAs prefer to store both the start date and end date in the table, but this too causes problems, as it is difficult to keep subsequent records in synch, especially when a modification occurs.*/

//Input:

//Output:

declare @Items table
(ID int, Name VARCHAR(1000))

//Sample Data
insert into @Items(ID,Name) values(1,'Vaccum Cleaner');
insert into @Items(ID,Name)values(2,'Washing Machine');
insert into @Items(ID,Name)values(3,'Tooth Brush');

//Price History Table Structure
declare @PriceHistory table(
ItemId int,
PriceStartDate datetime,
Price money
)

//Price History Table Data
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'03/01/2004',250.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'06/15/2005',219.99)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'01/03/2007',189.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'02/03/2007',200.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(2,'07/12/2006',650.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(2,'01/03/2007',550.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2005',1.99)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2006',1.79)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2007',1.59)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2008',1.49);

//Query to deliver the desired output.
WITH PriceCompare AS (
SELECT i.ID, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM @Items i INNER JOIN @PriceHistory ph
ON i.ID = ph.ItemId)


SELECT currow.ItemId, prevrow.Price AS PreviousPrice, currow.Price AS CurrentPrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate
FROM PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1
AND currow.ItemId = nextrow.ItemId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1
AND currow.ItemId = prevrow.ItemId

STR - for displaying the rounded decimal number

/* ROUND(expression,length )
* It can only contain the maximum range of decimal value
* Eg: 9223372.14554 -> 9223372.15

**********************************************************/

DECLARE @Number FLOAT
SET @Number = 92233720.14554
SELECT ROUND(@Number,2)

Generating Subtotals using Subtotal(in a single query),Grouping

//GROUPING
/* The GROUPING function is used as an additional column in the query, and this function will render the output value as '1' for the grand total row and '0' for the rest If we omit the ROLL UP the GROUPING function will not produce the desired output.

The ROLLUP operator is useful in generating reports that contain subtotals and totals.

*/

//Sample Data

SELECT

CASE WHEN (GROUPING(Item) = 1) THEN 'ALL' ELSE ISNULL(Item, 'UNKNOWN') END AS Item,

CASE WHEN (GROUPING(Color) = 1) THEN 'ALL' ELSE ISNULL(Color, 'UNKNOWN') END AS Color,

SUM(Quantity) AS QtySum

FROM Inventory GROUP BY Item, Color WITH ROLLUP

/*Individual investment record
* A separate row for the total amount invested and total broker commission *amount under each scheme
* A separate row for the total amount invested and total broker commission *amount under each product
*A single row for the total amount invested and total broker commission amount *under all products.
*************************************************************************************/
//Method 1:
//Create a Sample Table and Data


DECLARE @GroupTest TABLE
(
Product_Name varchar(100),
Scheme_Name varchar(100),
Invest_Amount numeric(18,2),
Broker_Commission numeric(18,2),
Issue_Date datetime
)




declare @cnt int, @idx int, @index int
set @cnt = 1
set @idx = 1
set @index = 1


while @cnt <=100 begin insert into @GroupTest values ( 'Product'+CAST(@idx AS VARCHAR), 'Scheme'+CAST(@index AS VARCHAR), ((@cnt * @idx / @index)* 1524.15),((@cnt * @idx / @index)* 124.32), GETDATE() ) if @cnt % 10 = 0 set @idx = @idx + 1 set @index = @index + 1 if @cnt % 5 = 0 set @index = 1 set @cnt = @cnt + 1 end //Individual investment record
SELECT
Product_Name,
Scheme_Name,
Invest_Amount,
broker_commission,
'0 Detail' AS Comment
FROM
@GroupTest ORDER BY Product_Name,Scheme_Name,Comment


/*A separate row for the total amount invested and total broker commission amount under each scheme */


SELECT
Product_Name,
Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'1 Scheme_Total'
FROM @GroupTest
GROUP BY Product_Name,Scheme_Name
ORDER BY Product_Name,Scheme_Name, Comment


/* A separate row for the total amount invested and total broker commission *amount under each product */
SELECT
Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'2 Product_Total' AS Comment
FROM @GroupTest
GROUP BY Product_Name
ORDER BY Product_Name, Scheme_Name, Comment


/*A single row for the total amount invested and total broker commission amount under all products. */
SELECT
'ZProductTotal' As Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'3 Grand_Total' AS Comment
FROM @GroupTest
ORDER BY Product_Name,Scheme_Name, Comment


/*Desired Output*/
SELECT
Product_Name,
Scheme_Name,
Invest_Amount,
broker_commission,
'0 Detail' AS Comment
FROM @GroupTest
UNION ALL
SELECT
Product_Name,
Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'1 Scheme_Total' as Comment
FROM @GroupTest
GROUP BY Product_Name,Scheme_Name
UNION ALL
SELECT
Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'2 Product_Total' AS Comment
FROM @GroupTest
GROUP BY Product_Name
UNION ALL
SELECT
'ZProductTotal' As Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'3 Grand_Total' AS Comment
FROM @GroupTest
ORDER BY Product_Name,Scheme_Name, Comment

// Method 2:

// Using Grouping function

SELECT

Product_Name,

Scheme_Name,

Invest_Amount,

broker_commission,

-1 AS GrandTotal

FROM @GroupTest

UNION ALL

SELECT

ISNULL(Product_Name,'ZProductTotal'),

ISNULL(Scheme_Name,'ZTotal'),

SUM(Invest_Amount) AS 'Invest_Amount',

SUM(broker_commission) AS 'Broker_Commission',

GROUPING(Product_Name) 'GrandTotal'

FROM @GroupTest

GROUP BY Product_Name,Scheme_Name

WITH ROLLUP ORDER BY Product_Name,Scheme_Name, GrandTotal

//Fig 1. With Grouping // Fig 2. With grouping and without ISNULL function