问题描述
我想创建具有在proc的WHERE部分的可选参数存储的过程。我的C#代码可以在空或为这个PROC一个有效的产品ID传递。在这里,它是:
申报@ProductID INT
组@ProductID = NULL
选择从
品
*
,其中
名称LIKE'%孩之宝%'
如果@ProductID不为空
开始
和产品id = @ProductID
端
这代码不起作用。如果产品编号为空,我希望它只是寻找产品与它的名字孩之宝。如果产品ID不为空,我希望它看起来的产品与它的名字孩之宝和匹配的产品ID。
谢谢!
更新:
下面是工作代码。谢谢大家帮助我得到这个!
申报@ProductID INT
组@ProductID = NULL
选择
*
从
产品
,其中
名称LIKE'%孩之宝%'
和((@ProductID为null)或( @ProductID不为null,产品ID = @ProductID))
这应该也行...
选择
* $ b $从
$产品b b $ b,其中
名称LIKE'%孩之宝%'和
(
(@ProductID为空)或
(@ProductID不是null的ProductID = @ProductID)
)
I would like to create a stored proc that has an optional parameter in the WHERE part of the proc. My C# code can pass in null or a valid product id for this proc. Here it is:
declare @ProductID int
set @ProductID = null
select
*
from
Products
where
Name like '%Hasbro%'
if @ProductID is not null
begin
and ProductID = @ProductID
end
This code doesn't work. If the product id is null, I want it to only look for products with the name 'Hasbro' in it. If the product id is not null, I want it to look for products with the name 'Hasbro' in it and for matching product ids.
Thanks!
UPDATE:
Here is the working code. Thank you everyone for helping me get this!
declare @ProductID int
set @ProductID = null
select
*
from
Products
where
Name like '%Hasbro%'
and (( @ProductID is null ) or ( @ProductID is not null and ProductID = @ProductID ))
This should work also...
select
*
from
Products
where
Name like '%Hasbro%' and
(
( @ProductID is null ) or
( @ProductID is not null and ProductID = @ProductID )
)
这篇关于如何包含可选的空参数在SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!