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,
