作者:Cri_Hello | 来源:互联网 | 2022-12-27 19:56
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 个解决方案