This week I was working with some triggers which had a lot of repeated queries which did exactly the same job, with the only difference is that each query inserted data into a different table. Each of the repeated queries were in an if statement to check if the location was correct and if it was the same location then the data was inserted into a location specific table.
When ever I see code that is repeated it makes my blood curl, I much perfer it when the code is simple and can be maintained easily, because who wants to come back next month, or next year and have to remember that all the queries must be updated, and whats worse what if the change is complex, then it means that each of the queries must have the same complex change made to it.
So I googled and found that there is a function is SQL called sp_executesql which can execute a sql command which is stored as a variable. So I took the original query and turned it into a string. And then concatenated the different table names, depending on the location to the end of the table name in the sql statement.
Have a look at the example code:
SELECT @locationsuffix = SUFFIX FROM LOCATIONMAPÂ WHERE LOCATION = @location
SET @sqlcommand = ‘SELECT @content=DATA.CONTENT ‘
+ ‘FROM DATA’ + @locationsuffix + ‘ AS DATA ‘
+ ‘WHERE DATA.NAME = ”’ + @name + ””
EXECUTE sp_executesql @sqlcommand, N’@content nvarchar(31) OUTPUT’, @content = @content OUTPUT
In this example the command executes the dynamic sql which selects data out a location specific table and returns the content back to the main context do that the content which was selected can be used further.
Please leave comments or feedback for me.