Some steps when converting Dynamic tsql to Static

Here is a list of things to keep in mind when converting dynamic tsql stored procedures into a static version (for performance enhancement).

1) Remove quotes (carefully). - Going through and eliminated the outside quotes is the easy part. You do have to be careful of the single inner quotes, for example a single quote(s) might be necessary in a varchar statement.

2) Look over the convert statements. - A majority of the convert statements, especially the converting to varchar will need to be looked over and possibly removed in most cases.

3) Replace variables that dealt with parsing. - Some stored procedures might be sending in multiple data into one variable for example @Y = ('1,2,3,4,5') and being accessed like: WHERE x in @Y. This will need to be replaced with either a PATINDEX or CHARINDEX.

4) Remove unnecessary declarations - Some variables are going to end up not being used now, for example the varchar variable that was used to make the dynamic tsql string.



These are the tips, I might remember some more and add them later.

Changing Dynamic SQL to Static SQL

I've been on this project for a couple of weeks now, changing store procedures that use dynamic sql to a static sql. The reason for this change is too speed up the store procedures.

The store procedure might have a dynamic sql statement like:

SET @tsql = 'SELECT * FROM TABLE1
WHERE TABLE1.Name = ' + @Name

IF @ID <> ''
@tsql = @tsql + ' AND TABLE1.ID = ' + @ID

exec sp_executeSql @tSqlQuery

This would be changed to:

SELECT * FROM TABLE1 WHERE TABLE1.Name = @Name
AND( (@ID <> '' AND TABLE1.ID = @ID) OR
(@ID = '' ))

Now came the problem if they dynamically set a column to be sorted:

IF LTRIM(RTRIM(@sortColumn)) <> ''
@tsql = @tsql + 'ORDER BY ' + @sortColumn

This unfortunately had to be solved by making a case statement for all possible columns that are returned. So in this case this table returns only two columns (name and id):

SELECT NAME, ID FROM TABLE1
WHERE TABLE1.Name = @Name AND ((@ID <> ''
AND TABLE1.ID = @ID) OR (@ID = '' ))
ORDER BY
CASE @sortColumn WHEN 'ID' THEN ID ELSE NULL END,
CASE @sortColumn WHEN 'NAME' THEN NAME ELSE NULL END

The reason for the seperate case statements in the example is because it can only return one data type. If NAME is of varchar and ID is of int, then they have to be separated.

Well, that's a very basic and simple run down of what I've been doing. I do run into larger more complex store procedures and other situations. For example, when a dynamic sql statement is using a table name as a variable.