如何在表名上使用CASE [英] How to use CASE on Table Names

问题描述

我正在尝试编写一个存储过程，我传递了两个参数：

@Table VARCHAR（20），  - 要搜索的表的名称

@SearchItems VARCHAR（120）  - 以逗号分隔的字符串列表

在文字中，我想要做的是告诉SQL要搜索哪个表以及在WHERE子句始终相同时返回哪些值。​​

这是我到目前为止所得到的（甚至不是有效的SQL）。 我只列出了一些CASE。 事实上，最终会有大约十几个CASE需要考虑。

 CASE @TABLE 
 WHEN' People1'
 THEN 
 SELECT LastName FROM People WHERE LastName IN（@ SearchItems）
 WHEN'Pople2'
 THEN 
 SELECT LastName，Birthdate FROM People WHERE CountryID IN（@ SearchItems）
 WHEN'目录'
 THEN 
选择标题FROM目录标题IN（@ SearchItems）
结束; 

我不喜欢知道如何继续。

解决方案

Erland大量讨论了使用&absp; 动态搜索条件。您还可以在where子句中找到许多关于CASE使用的问题。但是你首先需要理解CASE
是tsql中的表达式，而不是像大多数其他语言一样的流控制结构。你在tsql中使用它的方式非常不同。 

那就是说，你应该重新考虑你选择的路径有很多原因。程序应该被认为是一个黑盒子，并且应该产生一组"东西"。如果它返回结果集。 "事物"是指代表单个
实体的行。在这里，您似乎有一个catch-all过程，它返回包含来自不同表的未定义顺序的任意数量列的行。你没有明显的理由加入这种逻辑（没有任何好处 - 开发人员的懒惰是
不是真正的好处）。现在，当您需要以某种方式更改此过程时会发生什么（例如，向"目录"分支添加一列"）"？你不需要重新测试并验证所有的分支 - 这意味着验证这个程序的所有（或者一些样本
）这个程序的调用者（由于它的全部性质，人们可以假设它会很多）？ / p>

I am trying to write a Stored Procedure into which I pass two parameters:

@Table VARCHAR(20),  -- the name of the table to search
@SearchItems VARCHAR(120)  -- a comma separated list of strings

In words, what I want to do is tell SQL which table to search and what values to return while the WHERE clause is always the same.

Here is what I've got so far (which is not even valid SQL).  I've only listed a few of the CASEs.  In fact, eventually there will be about a dozen CASEs to consider.

CASE @TABLE
		WHEN 'People1'
			THEN
				SELECT LastName FROM People WHERE LastName IN (@SearchItems)
		WHEN 'People2'
			THEN
				SELECT LastName, Birthdate FROM People WHERE CountryID IN (@SearchItems)
		WHEN 'Catalog'
			THEN
				SELECT Title FROM Catalogs WHERE Title IN (@SearchItems)
	END;

I don't know how to proceed.

解决方案

Erland discusses in great deal the use of dynamic search conditions. You can also find many, MANY questions about the use of CASE in the where clause. But you first need to understand that CASE is an expression in tsql, not a control-of-flow construct like it is in most other languages. You use it very differently in tsql. 

That said, you should reconsider the path you have chosen for many reasons. A procedure should be considered a black box and should produce a set of "things" if it returns a resultset. By "things", I mean rows that represent a single entity. Here you seem to have a catch-all procedure that returns rows containing any number of columns in an undefined order from different tables. You have coupled this logic for no apparent reason (and no benefit - laziness on the part of the developer is not a true benefit). Now what happens when you need to alter this procedure in some fashion( say, add a column to the "Catalog" branch")? Will you not need to retest and validate all of the branches - which means validating all (or some sample of) the callers of this procedure (which one can assume will be many because of the catch-all nature)?

这篇关于如何在表名上使用CASE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！