存储过程检查参数是否为空

I am new to stored procedures, so I really need some help here. I am trying to modify a sp to show all records if the parameter is null, and if it is not null, there is another query for it to run. What I have is getting the error Procedure of function expects parameter @marketid which was not supplied. Can someone please help me resolve this issue?

我对存储过程不熟悉，所以这里需要一些帮助。我正在尝试修改一个sp来显示所有的记录，如果参数是null，如果它不是null，就会有另一个查询来运行。我得到的是函数的错误过程是参数@marketid，它没有被提供。有人能帮我解决这个问题吗?

ALTER  procedure [dbo].[sp_GetProductDetailsForMarket]
@marketid int

as

IF @marketid IS NOT NULL
begin
SELECT 
p.ProductID, p.pagenumber,p.ProductName,p.Description,p.CategoryID,
   p.ItemNumber,p.Price,p.AlertFrequency,p.PrimaryImageName,
   p.DeletionStateCode,p.Published,p.SearchTerms,p.ThumbNailPath,
   p.UOMName,p.HCPCCode,
   m.marketname,mm.MarketID,
   pa.AttributeID,pa.AttributeValueID,pa.Price as AttributePrice,
   pa.ImagePath,pa.ProductAttributeAssociationID,pa.IsOptional,
   av.[Name] as AttributeValueName,a.[Name] as AttributeName

FROM vPanel_Product p 
INNER JOIN vPanel_MarketMappings mm
        on p.productid=mm.productid
LEFT JOIN vPanel_Market m
        on m.marketid=mm.marketid
LEFT JOIN vPanel_ProductAttributeAssociation pa
       on p.productid = pa.productid
        and pa.deletionstatecode=0
LEFT JOIN vPanel_Attributes a
        on a.AttributeID = pa.AttributeID
        and a.deletionstatecode=0
LEFT JOIN vPanel_AttributeValues av
        on av.attributeValueID = pa.attributeValueID
        and av.deletionstatecode=0
WHERE m.marketid = @marketid
order by p.productname,A.ATTRIBUTEID,AV.ATTRIBUTEVALUEID
end

ELSE 

begin
SELECT 
p.ProductID, p.pagenumber,p.ProductName,p.Description,p.CategoryID,
   p.ItemNumber,p.Price,p.AlertFrequency,p.PrimaryImageName,
   p.DeletionStateCode,p.Published,p.SearchTerms,p.ThumbNailPath,
   p.UOMName,p.HCPCCode,
   pa.AttributeID,pa.AttributeValueID,pa.Price as AttributePrice,
   pa.ImagePath,pa.ProductAttributeAssociationID,pa.IsOptional,
   av.[Name] as AttributeValueName,a.[Name] as AttributeName

FROM vPanel_Product p 
LEFT JOIN vPanel_ProductAttributeAssociation pa
       on p.productid = pa.productid
        and pa.deletionstatecode=0
LEFT JOIN vPanel_Attributes a
        on a.AttributeID = pa.AttributeID
        and a.deletionstatecode=0
LEFT JOIN vPanel_AttributeValues av
        on av.attributeValueID = pa.attributeValueID
        and av.deletionstatecode=0
WHERE @marketid IS NULL
ORDER BY p.productname,A.ATTRIBUTEID,AV.ATTRIBUTEVALUEID
end

2 个解决方案

#1

Add the default value to your input paramtere ie NULL

将默认值添加到您的输入paramtere ie NULL。

ALTER  procedure [dbo].[sp_GetProductDetailsForMarket]
@marketid int = NULL

So when you will call your stored procedure like this:

当你这样调用存储过程时

exec sp_GetProductDetailsForMarket

it will not show an error as not the default value of your input parameter @marketid is now set to NULL

它不会显示错误，因为输入参数@marketid的默认值现在设置为NULL

#2

Your problem is not in your stored procedure. Your problem is that your calling application isn't passing the parameter in. You could default it to null if you want, like this:

您的问题不在存储过程中。您的问题是您的调用应用程序没有传入参数。如果你想，你可以默认它为空，比如:

ALTER  procedure [dbo].[sp_GetProductDetailsForMarket]
@marketid int = NULL

Or, you could just always pass it in, null or not.

或者，你可以把它传入，null或not。

#1