Execute properly even if no parameter value is supplies

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================

-- =============================================
ALTER PROCEDURE [dbo].[Product_FindByParameters]
(
@.Name Varchar(255),
@.ManufactureID bigint,
@.ShortDescription Varchar(255),
@.ManufactureProductID Varchar(255),
@.ItemsInStock bigint,
@.StorePartNumber Varchar(255)

)


AS
BEGIN

SELECT P.ProductId,
P.StorePartNumber,
P.ShortDescription,
P.ManufactureProductID,
P.Name,
P.Price,
P.ItemsInStock,
M.ManufactureName
FROM Product P left join Manufacture M
ON P.ManufactureID=M.ManufactureID
WHERE
( P.Name like '%' + @.Name + '%' OR @.Name is null)
AND (P.ShortDescription LIKE '%' + @.ShortDescription + '%' OR @.ShortDescription is null)
AND( P.ManufactureProductID LIKE '%' + @.ManufactureProductID + '%' OR @.ManufactureProductID is null)
AND (P.ItemsInStock=@.ItemsInStock)
AND (P.ManufactureID = @.ManufactureID OR @.ManufactureID is null)
END

--exec [dbo].[Product_FindByParameters] 'Heavy-Duty ',7,'Compact Size','DC727KA' ,0,''
--exec [dbo].[Product_FindByParameters] 'Heavy',7,'','','',''
--exec [dbo].[Product_FindByParameters] 'Heavy','' ,'','','' ,''

First 2 exec statement gives many data row as result,

But why the last donot give any row ;( ;(

how can i rewrite the stored procedure, such that it gives out put even if i don't supply ManufactureID as input\

kindly help me

Hi There

You miss out 'OR CASE' for 'ItemsInStock'

sujithukvl@.gmail.com:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================

-- =============================================
ALTER PROCEDURE [dbo].[Product_FindByParameters]
(
@.Name Varchar(255),
@.ManufactureID bigint,
@.ShortDescription Varchar(255),
@.ManufactureProductID Varchar(255),
@.ItemsInStock bigint,
@.StorePartNumber Varchar(255)

)


AS
BEGIN

SELECT P.ProductId,
P.StorePartNumber,
P.ShortDescription,
P.ManufactureProductID,
P.Name,
P.Price,
P.ItemsInStock,
M.ManufactureName
FROM Product P left join Manufacture M
ON P.ManufactureID=M.ManufactureID
WHERE
( P.Name like '%' + @.Name + '%' OR @.Name is null)
AND (P.ShortDescription LIKE '%' + @.ShortDescription + '%' OR @.ShortDescription is null)
AND( P.ManufactureProductID LIKE '%' + @.ManufactureProductID + '%' OR @.ManufactureProductID is null)
AND (P.ItemsInStock=@.ItemsInStock OR@.ItemsInStock is null)
AND (P.ManufactureID = @.ManufactureID OR @.ManufactureID is null)
END

--exec [dbo].[Product_FindByParameters] 'Heavy-Duty ',7,'Compact Size','DC727KA' ,0,''
--exec [dbo].[Product_FindByParameters] 'Heavy',7,'','','',''
--exec [dbo].[Product_FindByParameters] 'Heavy','' ,'','','' ,''

|||

In your input parameter, give the default NULL value to ManufactureID. With that, you can do OR @.ManufactureID = NULL so that you can ignore it when the value doesn't provide.

@.ManufactureID bigint = NULL,