可选参数值，好还是不好？ [英] Optional parameter values, good or not ?

问题描述

 声明  @ Param1   int  
   -    set @Param1 = -1  
   -    set @Param1 = 123  
 
   -    Field1永远不会等于-1  
 
 选择 field1，* 来自表1 
 其中（ @Param1  = -1）或 
（field1 =  @ Param1 ）

如果将 @Param1 设置为 -1 ，然后从表中获取所有数据，但如果将参数设置为特定值，则只获得等于该值的行。



这种查询确实可以节省编码时间。对于小桌子（和少量的请求），这真的是一个很好的方法，但我从来没有时间去看它是否是正确的方法？因为当您拥有数百万行数据和大量请求时，正确的方法非常重要。



任何人???

解决方案

我写的是一个通用报告应用程序。用户可以使用特定值来微调任意数量的字段。留空的字段通常意味着接受该字段中的所有数据。



因此，当我编写代码以允许这些报告时，DBA使用他的值存储过程在case语句（或者更确切地说是一系列）中处理这些通配符语句，通常在WHERE子句中。



类似的东西（假装它是一个int field）

  WHERE  intCol =  CASE   @ ival  
  WHEN  -1 那么 intCol 
  else   @ ival  
 结束 

关键是一切都是通过比较列的值来实现的。他知道各种表演的东西，所以我想如果这是不好的练习，他就不会用它。



这些也可以在SELECT列表中出现



请参阅： CASE（Transact-SQL） [ ^ ]

declare @Param1 int
-- set @Param1 = -1
-- set @Param1 = 123

-- Field1 is never equal to -1

select field1, * from Table1
  where (@Param1 = -1) or
        (field1 = @Param1)

If you set the @Param1 to -1, then you get ALL data from the table, but if you set the parameter to a specific value then you only get the rows that are equal to that value.

This kind of query is really a time saver for coding. For small tables ( and small quantity of requests) this is really a great way to do it, but I never had the time to look if it is the right way? Because the right way is very important when you have a millions of rows of data and a very large number of requests.

Anyone ???

解决方案

There's a generic reporting application I wrote. Users can use specific values to fine tune any of an arbitrary number of fields. Fields left empty typically imply that all data in that field is to be accepted.

So, whilst I wrote code to allow these reports the DBA that uses the values in his stored procedures handles these wild-card statement in a case statement (or rather, a series of them), usually in the WHERE clause.

Something like (pretend it's an int field)

WHERE intCol = CASE @ival
  WHEN -1 THEN intCol
  else @ival
END

The point being that the match everything is accomplished by comparing a column's value to itself. He knows all sorts of performance stuff, so I'd imagine he'd not use this if it were bad practice.

These can also turn up in the SELECT list

See: CASE (Transact-SQL)[^]

这篇关于可选参数值，好还是不好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！