About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Thursday, July 26, 2007

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.