Solved

Sql Server dynamic filtering in a Stored procedure

Posted on 2008-10-10
5
205 Views
Last Modified: 2012-05-05
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
Comment
Question by:Nyana22
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22686333
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22686348
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22686377
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22686382
Guess I need more coffee :)
0
 

Author Comment

by:Nyana22
ID: 22686400
:D
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question