Flexible Select Procedure

Question

Post reply

Flexible Select Procedure

James Spibey

SSC Enthusiast

Points: 128
More actions
November 16, 2004 at 9:17 am

#60652

Hi,
I am trying to write a stored procedure to select a recordset. However, I need to be able to change the WHERE clause of the procedure depending on the value of one of the parameters. I tried something like the following but obviously it doesn't work. What is the correct way to do it?
CREATE PROCEDURE ListMyTable( @Op As Integer, @ParamValue As Integer)
AS
SELECT TABLE_ID FROM MY_TABLE
IF @Op = 1
WHERE COL1 = @ParamValue
ELSE IF @Op = 2
WHERE COL2 = @ParamValue
GO

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 1

CREATE PROCEDURE ListMyTable( @Op As Integer, @ParamValue As Integer)

AS

IF @Op = 1

select table_id from mytable WHERE COL1 = @ParamValue

ELSE IF @Op = 2

select table_id from mytable WHERE COL2 = @ParamValue

GO

James Spibey SSC Enthusiast Points: 128 More actions · Answer 2

Thanks for that Steve.

Unfortunately my SELECT prodcedure is much more complicated than the one illustrated. The reason I'm doing this is that I don't want to have to maintain multiple column lists as this is a nightmare to maintain. Is there a way to do it with one SELECT procedure and tack the WHERE clause on afterwards?

Osoba SSCommitted Points: 1964 More actions · Answer 3

try this out in query analyzer(it doesn't paste well in the thread)....

declare @op int

declare @paramvalue int

(set variables to a value for test)

declare @sql_script varchar(2000)

set @sql_script='select table_id from my_table where col'+@op+' ='+@paramvalue+''

exec (@sql_script)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

See, if this helps:

http://www.sommarskog.se/dyn-search.html

http://www.sommarskog.se/dynamic_sql.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sthornton Ten Centuries Points: 1333 More actions · Answer 5

Hi,

Why don't you use:

WHERE

(@Op = 1 AND COL1 = @ParamValue)
OR
(@Op = 2 AND COL2 = @ParamValue)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

I had some very good luck using a CASE statement in a WHERE clause for just this purpose. Try something like this...

CREATE PROCEDURE ListMyTable( @Op As Integer, @ParamValue As Integer) AS SELECT TABLE_ID
FROM MY_TABLE WHERE 1 = CASE ---- CASE "TESTS" RUN IN ORDER LISTED
WHEN @Op = 1
AND COL1 = @ParamValue
THEN 1
WHEN @Op = 2
AND COL2 = @ParamValue
THEN 1
ELSE 0 -- You MUST have this line for this to work!!!
END
GO

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

James Spibey SSC Enthusiast Points: 128 More actions · Answer 7

Thanks for all the excellent replies. In the end I had the most success with Jeff's approach.

Thanks again!

George Hayduke SSC Enthusiast Points: 145 More actions · Answer 8

I've used a twist on the above use of CASE for controlling the WHERE clause depending on the values of input parameters. Something like:

SELECT *

FROM MyTable

WHERE Col1 = CASE @Parameter1

WHEN 1 THEN 'A'

WHEN 2 THEN 'B'

ELSE 'Other' END

You could modify how you use the CASE to accomodate ranges and more complex logic.