Stored Procedure Check if Parameter is NULL

Question

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

Answer 1

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.

Answer 2

Add the default value to your input 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