SQL Server动态查询 [英] SQL Server dynamic queries

问题描述

我有15个存储过程从普通表返回数据，然后使用特定的表加入该表以检索库存。

I have 15 stored procedures that return data from a common table and then join that table with a specific table to retrieve inventory.

示例：

Common: tblCommon
Specific: tblSpecific

有没有办法可以将名称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

推荐答案

这样做的方式是通过sp_executesql（）存储过程运行的动态生成的SQL。

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

一般来说，您将所需的表名称传递给主程序，构建要执行的SQL的ncharvar字符串，并将其传递给sp_executesql。

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.

动态SQL的诅咒和祝福

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

最大的困扰之一是，如果您使用动态SQL，则调用存储过程的用户不仅必须具有该过程的执行权限，还必须有权访问基础表。我给出的链接也描述了如何解决这个问题。

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.

这篇关于SQL Server动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！