The Old Joel on Software Forum: Part 3 (of 5)

SQL Question

Is there a way to write a stored procedure such that it can take an unspecified number of parameters, and structure the query based on the number of parameters? I'll give an example of what I'm doing:

CREATE PROC spGetPostings

@Category1 int
@Category2 int
@Category3 int

@Location1 int
@Location2 int

AS

SELECT Posting.PostingCode, Job.JobCode, Job.Title, Job.Description, Area.StateCode, Area.Name, Posting.Hours, Posting.IsBonusEligible
FROM Posting
INNER JOIN Job
    ON Posting.JobID = Job.JobID
INNER JOIN Area
    ON Area.AreaID = Job.AreaID
WHERE
-- search through categories first
(
    (Job.PrimCategory = @Category1 OR Job.SecCategory = @Category1)
    OR
    (Job.PrimCategory = @Category2 OR Job.SecCategory = @Category2)
    OR
    (Job.PrimCategory = @Category3 OR Job.SecCategory = @Category3)
)
-- then search through areas
AND
(
    (Job.AreaID = @Location1)
    OR
    (Job.AreaID = @Location2)
)

If there is just one location parameter, I'd like to eliminate one of the OR clauses. But if there are four location parameters, I'd like to have four OR clauses.

Any suggestions?

easier is better
Friday, October 31, 2003

You can take the input parameter as a CSV list and then parse it inside the SP.

EastIndian
Friday, October 31, 2003

If you do have an absolute max on the number of parameters (like three, in your example), then:

CREATE PROC spGetPostings

@Category1 int
@Category2 int
@Category3 int

@Location1 int
@Location2 int

AS

SELECT [columns]
FROM Posting
WHERE
-- search through categories first
(
(Job.PrimCategory = @Category1 OR Job.SecCategory = @Category1)
OR
(@Category2 IS NULL OR Job.PrimCategory = @Category2 OR Job.SecCategory = @Category2)
OR
(@Category3 IS NULL OR Job.PrimCategory = @Category3 OR Job.SecCategory = @Category3)
)

If you need an arbitrary number of criteria, then you have to send in a comma delimited list of criteria as a varchar, get a SPLIT function for the RDBMS you're using, and run an IN clause.

Philo

Philo
Friday, October 31, 2003

Ya, taking it as a parameter is what I believe I have to do, I just don't know where to begin generating the OR clauses based on the parsed parameter...

easier is better
Friday, October 31, 2003

You're talking about SQL Server right?

SQL Server doesn't support variable length parameter lists unless you hard code a specific number and use defaults ("@category1 int = -1, @category2 int = -1, " ...), so instead have:

@categoryList varchar(n)
and
@locationList varchar(n)

and pass the ints as comma-separated values - use CHARINDEX etc to unpack the ints from the comma-delimited list.

As for the query, it sounds like you'll need to dynmically create the SQL i.e. get the sp to create a varchar called @sql with the right statement, and then finally do an "EXEC @sql"

It "smells" wrong - but does the trick. Although, when it comes to doing things like this you may be more comfortable doing the dynamic SQL creation in the application code (instead of T-SQL inside the sp) and passing this to SQL Server.

Duncan Smart
Friday, October 31, 2003

Here's an example of using a parameter list:

CREATE PROCEDURE dbo.GetDisplayPrograms
(
@Programs varchar(1000)
)
AS
SELECT
[fields]
FROM [table]
WHERE
ProgramID
IN (SELECT value FROM dbo.fn_Split(@Programs, ','))

fn_Split is a user-defined function that splits a varchar on a character.

Philo

Philo
Friday, October 31, 2003

I'm no SQL guru, but I do know MS SQL Server has excellent XML support ...

HTH
Friday, October 31, 2003

thanks guys!

easier is better
Friday, October 31, 2003

easier is better,

Using sp_executesql inside your stored proc may be useful.

Seeya

Matthew
Sunday, November 2, 2003