存储过程，将表名作为参数传递 [英] Stored procedure, pass table name as a parameter

问题描述

我有大约六个通用但相当复杂的存储过程和函数，我想以更通用的方式使用它们.

I have about half a dozen generic, but fairly complex stored procedures and functions that I would like to use in a more generic fashion.

理想情况下，我希望能够将表名作为参数传递给过程，因为目前它是硬编码的.

Ideally I'd like to be able to pass the table name as a parameter to the procedure, as currently it is hard coded.

我所做的研究表明我需要将程序中所有现有的 SQL 转换为使用动态 SQL，以便从参数中拼接动态表名，但是我想知道是否有更简单的方法来引用表以另一种方式?

The research I have done suggests I need to convert all existing SQL within my procedures to use dynamic SQL in order to splice in the dynamic table name from the parameter, however I was wondering if there is a easier way by referencing the table in another way?

例如:

SELECT * FROM @MyTable WHERE...

如果是这样，我如何从表名设置@MyTable 变量?

If so, how do I set the @MyTable variable from the table name?

我使用的是 SQL Server 2005.

I am using SQL Server 2005.

推荐答案

动态 SQL 是执行此操作的唯一方法，但如果需要，我会重新考虑您的应用程序的架构.SQL 不太擅长通用"代码.当它被设计和编码来完成单个任务时效果最好.

Dynamic SQL is the only way to do this, but I'd reconsider the architecture of your application if it requires this. SQL isn't very good at "generalized" code. It works best when it's designed and coded to do individual tasks.

从 TableA 中选择与从 TableB 中选择不同，即使 select 语句看起来相同.可能有不同的索引、不同的表大小、不同的数据分布等.

Selecting from TableA is not the same as selecting from TableB, even if the select statements look the same. There may be different indexes, different table sizes, data distribution, etc.

您可以生成单独的存储过程，这是一种常见的方法.拥有一个代码生成器，可为您需要的表创建各种选择存储过程.每个表都有自己的 SP，然后您可以将其链接到您的应用程序中.

You could generate your individual stored procedures, which is a common approach. Have a code generator that creates the various select stored procedures for the tables that you need. Each table would have its own SP(s), which you could then link into your application.

我已经用 T-SQL 编写了这些类型的生成器，但是您可以使用大多数编程语言轻松完成.这是非常基本的东西.

I've written these kinds of generators in T-SQL, but you could easily do it with most programming languages. It's pretty basic stuff.

自从 Scott E 提出 ORM 以来，再补充一点……您还应该能够将这些存储过程用于最复杂的 ORM.

Just to add one more thing since Scott E brought up ORMs... you should also be able to use these stored procedures with most sophisticated ORMs.

这篇关于存储过程，将表名作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！