Sql Server Dynamic Queries

I have 15 stored procedures that return data from a common table and then join that table with a specific table to retrieve inventory. Example: Common: tblCommon Specific: tblSpeci

Solution 1:

The way you do this is with dynamically generated SQL which is run through the sp_executesql() stored procedure.

In general you pass in your required table name to your master procedure, build an ncharvar string of the SQL you want to execute, and pass that to sp_executesql.

The curse and blessing of Dynamic SQL is about the best page I have seen for describing all the in's and out's.

One of the biggest gotchas is that if you use dynamic SQL then the user who calls your stored procedure not only has to have execute permission on that procedure, but also has to have permission to access the underlying tables. The link I gave also describes how to get around that issue.

Solution 2:

Yep, you can generate an SQL statement dynamically and then execute it.

For example,

DECLARE@specificTableName nvarchar(50)
DECLARE@specificColumnName nvarchar(50)

SET@specificTableName='tblSpecific'SET@specificColumnName='colSpecific'DECLARE@sql nvarchar(4000)

set@sql='SELECT ... FROM tblCommon c INNER JOIN '+@specificTableName+' s ON c.PrimaryKey = s.'+@specificColumnNameexec (@sql)

Solution 3:

Formulate/manipulate your query as a string, then call EXECUTE(@SQLStatement)

Solution 4:

Dynamic SQL is dangerous. You never want to substitute passed values directly into an SQL string. Fortunately, it sounds like you already know that.

Unfortunately, in this case you've discovered the problem that you can't use an SQL parameter for the table name. So, what to do? You don't want to use the passed value in dynamically generated SQL, but you can't put it in a query in the normal safe way.

The answer is a lookup table. Create a 'tables' table that holds the name of each of your specific tables. It should look kind of like this:

CREATE TABLE[tables] (table_name sysname)

Then you can write a query that looks something like this:

SELECT@tblSpecific= table_name FROM [tables] WHERE table_name =@tblSpecific

Now you just have to check whether @tblSpecific is NULL. If it's not, then it's safe to use in a dynamic SQL statement (and dynamic SQL is ultimately your only option here: even the user defined function has you doing that at some level).

Oh, and one more thing--my choice of names and types for the lookup table is not an accident. The SQL Standard already has a table like this (well, a view anyway). Just use INFORMATION_SCHEMA.Tables.

Solution 5:

An alternative, if the amount of data isn't too large you may want want to consider a user defined function, which can return a table variable which you could use to join to.

SELECT ....
FROM tblCommon c
INNERJOIN dbo.SomeFuntionThatReturnsData(@someparam) s on c.primaryKey = s.foreignKey