Stored Procedure Check if Parameter is NULL
sql, sql-server, stored-procedures
Solution
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.
Problem
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? ``` 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 ```