[SOLVED] Stored procedure with optional parameter to function in WHERE clause

Transact-SQL
1

I need to query a database with a stored procedure by passing it parameters. However one of those parameters needs to be optional & it is one that is important to the WHERE clause. I can't wrap my mind around how to make it work.

Let's say that my table looks like this:

CREATE TABLE #Products(
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] nvarchar NULL,
[Title] nvarchar NULL,
[DateAdded] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]

INSERT INTO #Products (ProductID, Title) VALUES ('SKU-001','This is the first sku');
INSERT INTO #Products (ProductID, Title) VALUES ('SKU-002','This is the second sku');
INSERT INTO #Products (ProductID, Title) VALUES ('SKU-003','This is the third sku');
INSERT INTO #Products (ProductID, Title) VALUES ('SKU-004','This is the fourth sku');
INSERT INTO #Products (ProductID, Title) VALUES ('SKU-005','This is the fifth sku');

And the stored procedure to collect information from that table looks like this:

CREATE PROCEDURE #uspSearchProducts
@ProductID NVARCHAR(400) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT *
FROM #Products
WHERE ProductID = @ProductID
END
GO

If I call it with the parameter, it works perfectly:

EXEC #uspSearchProducts @ProductID='SKU-001'

But if I call it without the parameter, it just gives me a blank response:

EXEC #uspSearchProducts

How would I make it give me everything unless filtered by the parameter?

2

ALTER PROCEDURE #uspSearchProducts
@ProductID NVARCHAR(400) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @LEN INT

SET @LEN = 1

iF ( @LEN < LEN(@ProductID))BEGIN

-- Insert statements for procedure here
SELECT *
FROM #Products
WHERE
ProductID = @ProductID
END
ELSE

SELECT *
FROM #Products

end

GO

3 
CREATE PROCEDURE #uspSearchProducts
@ProductID nvarchar(400) = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT * 
FROM #Products
WHERE (@ProductID IS NULL OR ProductID = @ProductID)
END
GO
1 Like
4

So simple & yet completely perfect. Thank you!