SQL Server dynamic queries

Question

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: tblSpecific

Is there way I can pass the name "tblSpecific" into a single stored procedure as a variable, like the following?

SELECT ....
FROM tblCommon c
INNER JOIN @TABLE s on c.primaryKey = s.foreignKey

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.' + @specificColumnName


exec (@sql)

Solution 3

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 4

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

View more solutions

Comments

[Peter M]

As you can't use a table name as a parameter, then you will still have to explicitly write out all of your SQL in individual statements at some point.

[Super Engineer]

Yeah, but my point was the funtion might be able to replace the 15 procs and used to return a common table variable to join to.

[Admin]

That's just reactionary to say NEVER. You never want to do so IF the value is gathered from a user screen... but if you create the domain for that variable it's safe.. just as safe as if he wrote one query per table and put them in a case statement. Some people have no sense of moderation.

[Admin]

Amen... People get lost in the idea of non-repeating code. "If I put it in one place it will be really cool." The downside is that when changes need to be made you'll need to fix it in a few places but there's a big chance that one of these 15 tables will look different and need to come out anyways.

[dkretz]

This is one of the many ways that good coding style for declarative code isn't equivalent to good coding style for procedural code. There aren't many transferable skills between the two. IOW, excellent procedural skills doesn't imply much about how good you will be at SQL.