Dynamic SQL Problems

Question

Dynamic SQL Problems

mjackson-1128654

SSC Rookie

Points: 28
More actions
June 29, 2010 at 11:29 am

#140890

We have a legacy database which contains a configuration table. Many parameters are stored in that table. Typically a stored procedure queries the table for a parameter value, creates a dynamic SQL string, and uses sp_execute to execute the string. An example might be selecting price data. The actual database for the price data is in the config table. So:
SELECT @dbname = TheName FROM ConfigData WHERE ParamName='PriceDatabase'
SELECT @theString = 'SELECT fValue FROM ' + @dbname + '.dbo.tblPrice'
EXEC sp_execute(@theString)
Generically, is there a better way to handle this kind of situation?
Thx
Mark Jackson

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

It depends. You could be trading some performance for this approach. The query will not be able to take advantage of caching certain elements, such as query plan or procedure cache.

I would recommend testing against a more standard way of launching a procedure to see of you are really paying penalty.

Converting oxygen into carbon dioxide, since 1955.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Use the lookup tables to generate views in the current database that point to tables in the other database.

You only have to regenerate the views when the source datatabase or table name changes. The queries you write will stay the same, since the view name will not change.

SELECT

@dbname = TheName

FROM

ConfigData

WHERE

ParamName='PriceDatabase'

SELECT @theString =

'create view dbo.Price as SELECT * FROM ' + @dbname + '.dbo.tblPrice'

EXEC sp_execute(@theString)

mjackson-1128654 SSC Rookie Points: 28 More actions · Answer 3

Problem is that some of these parameters can change frequently so re-creating these views and keeping track of them would be a big problem.

WayneS SSC Guru Points: 95460 More actions · Answer 4

mjackson-1128654 (6/29/2010)
We have a legacy database which contains a configuration table. Many parameters are stored in that table. Typically a stored procedure queries the table for a parameter value, creates a dynamic SQL string, and uses sp_execute to execute the string. An example might be selecting price data. The actual database for the price data is in the config table. So:
SELECT @dbname = TheName FROM ConfigData WHERE ParamName='PriceDatabase'
SELECT @theString = 'SELECT fValue FROM ' + @dbname + '.dbo.tblPrice'
EXEC sp_execute(@theString)
Generically, is there a better way to handle this kind of situation?
Thx
Mark Jackson

Mark,

If the dynamic sql that you are creating can run with a parameter (the example above won't), then I'd suggest parameterizing the query, and calling it with sp_executesql. This will allow you to pass the parameters, and then the query will be able to utilize cached query plans.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2