参数化存储过程
答
你可以让你WHERE
条件是这样的:
WHERE (@myParam IS NULL OR @myParam = someValue)
您可以使用OPTION (RECOMPILE)
是SQL2008SP1 +(或类似的,不知道其他选项),这取决于你的RDBMS,以获得这个性能。
从厄兰Sommarskog方法:
http://www.sommarskog.se/dyn-search-2008.html#static
从链接: “所有的@x的效果是NULL的条款是,如果输入参数为NULL,那么AND条件始终是因此,唯一有效的条件是那些搜索参数具有非NULL值的条件。
只要可维护性得到提高,就很难为目前的搜索条件想出更好的解决方案。紧凑,易于阅读和扩展。而且性能非常好只要你包含查询提示OPTION(RECOMPILE)就可以了。这提示强制每次重新编译查询,其中,好像他们是常数的情况下,SQL Server将使用实际变量值。”
答
如果它是一个int
可以使用
SELECT X,Y
FROM T
WHERE C BETWEEN COALESCE(@P, -2147483648) AND COALESCE(@P, 2147483647)
答
我建议参数化的动态SQL(sp_executesql)
走这条路,你可以建立你的where子句时丢弃任何不相关的参数。
示例程序:
create proc dbo.SearchForStuff
(
@Id int = 0
,@Description varchar(100) = ''
)
as
begin
set nocount on;
declare @select nvarchar(max) = '
select
s.*
from Stuff as s'
declare @where varchar(max) = ''
if isnull(@ID,0) != 0 begin
set @where += case @where when '' then ' where ' else ' and ' end + 's.Id = @Id'
end
if isnull(@Description,'') != '' begin
set @where += case @where when '' then ' where ' else ' and ' end + 's.[Description] = @Description'
end
set @select += @where
exec sp_executesql
@select
,N'
,@Id int = 0
,@Description varchar(100) = '''''
,@Id
,@Description
end
用法:
exec SearchForStuff @Id = 1, @Description = 'omg' -- Returns every item where Id is 1 and Description is 'omg'
exec SearchForStuff @Id = 1 -- Returns every item where Id is 1
exec SearchForStuff @Description = 'omg' -- Returns every item where Description is 'omg'
exec SearchForStuff --returns every item
以这种方式最终的查询是不是无用的条件散落。此外,你可以比我在这里得到更多的粒度。根据哪些参数通过,您可以定制您的where/join子句以利用您的索引,从而获得最佳性能。唯一的缺点是轻微丢失的可读性(IMO)。
查询中需要一个条件语句,但不建议在单个查询中完成 - 不是可靠的。 – 2011-05-06 20:39:54
我认为有一种方法,您可以将参数的默认值设置为NULL或什么,这将让我执行存储过程,而无需使用参数..我有道理..我不知道.. – Avinash 2011-05-06 20:42:56
@OMG小马取决于它是如何写入的,你可以使用'OPTION RECOMPILE'来增加可用性,不是吗? – Matthew 2011-05-06 20:45:46