• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Sql Server dynamic filtering in a Stored procedure

Hello,
I am writing the following stored procedure, i want depending on the input parameter to set the filter expression of the where clause,
But i am getting errors, can someone help please?
ALTER PROCEDURE [dbo].[interConfParam] 
(
 
@PType nvarchar(20)
)
 
AS
BEGIN
DECLARE @ProductType nvarchar(50)
SELECT @ProductType = CASE @PType WHEN 'ALL' THEN 'IN ('Body', 'Plug')' ELSE @Ptype END 
SELECT     S2ID, ProductType
FROM         SrDDW
WHERE     (ProductType = @ProductType)
 
END

Open in new window

0
Nyana22
Asked:
Nyana22
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will do:
ALTER PROCEDURE [dbo].[interConfParam] 
(
 
@PType nvarchar(20)
)
 
AS
BEGIN 
SELECT     S2ID, ProductType
FROM         SrDDW
WHERE   (   ( @PType = 'ALL' AND ProductType IN ('Body', 'Plug'))
         OR ( ProductType =  @PType )
        )

Open in new window

0
 
chapmandewCommented:
ALTER PROCEDURE [dbo].[interConfParam]
(
 
@PType nvarchar(20)
)
 
AS
BEGIN
DECLARE @sql nvarchar(4000)
DECLARE @ProductType nvarchar(50)
SELECT @ProductType = CASE @PType WHEN 'ALL' THEN 'IN ('Body', 'Plug')' ELSE @Ptype END
SET @sql = 'SELECT     S2ID, ProductType
FROM         SrDDW
WHERE     (ProductType IN(' + CASE @Ptype When 'ALL' THEN '''Body'', ''Plug''' ELSE @PType END + ')'
exec sp_executesql @sql
END
 
0
 
Patrick MatthewsCommented:
ALTER PROCEDURE [dbo].[interConfParam]
(
 
@PType nvarchar(20)
)
 
AS
BEGIN
DECLARE @ProductType nvarchar(50), @sql varchar(max)

SET @sql = 'SELECT S2ID, ProductType FROM SrDDW WHERE ProductType '

IF @PType = 'ALL'
    SET @sql = @sql + 'IN (''Body'', ''Plug'')'
ELSE
    SET @sql = @sql + '= ''' + @Ptype + ''''
END

EXEC(@sql)
 
END
0
 
Patrick MatthewsCommented:
Guess I need more coffee :)
0
 
Nyana22Author Commented:
:D
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now