Variable parameters

Question

Post reply

Variable parameters

Dave Sharp

Newbie

Points: 1
More actions
April 12, 2005 at 10:35 pm

#66186

I have 6 parameters in my SP. The user may specify a value for any combination of the parameters, or leave any/all parameters blank. If the user enters a value for a parameter, I have to limit the records returned by that field. If the user leaves a parameter blank, I don't limit the records returned.
How can I build a "variable" SQL statement in my SP? I need to be able to do something like this:
CREATE PROCEDURE MySQLSP
@P1 nvarchar(2),
@P2 nvarchar(2),
@P3 nvarchar(2),
@P4 nvarchar(2),
@P5 nvarchar(2),
@P6 nvarchar(2)
AS
Select * From MySQLTable
Where 1=1
      If @P1 <> "" Then
          And Field1Value = @P1
      Endif
      If @P2 <> "" Then
         And Field2 = @P2
      Endif
      If @P3 <> "" Then
          And Field3 = @P3
      Endif
      If @P4 <> "" Then
         And Field4 = @P4
      Endif
      If @P5 <> "" Then
         And Field5 = @P5
      Endif
      If @P6 <> "" Then
         And Field6 = @P6
      Endif
Order By MyIndexedField
How can I do this? Thanks for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

Try something like

CREATE PROCEDURE MySQLSP

@P1 nvarchar(2)=Null,

@P2 nvarchar(2)=Null

AS

Select * From MySQLTable

Where Field1Value = isnull(@P1, Field1Value)

and And Field2 = isnull(@P2, Field2)

Order By MyIndexedField

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 2

If none of Field1 - Field6 are nullable (or contain nulls), one way to do this is:

Select * From MySQLTable

Where Field1 = CASE P1 WHEN '' THEN Field1 Else @P1 END

and Field2 = CASE P2 WHEN '' THEN Field1 Else @P2 END

and Field3 = CASE P3 WHEN '' THEN Field1 Else @P3 END

and Field4 = CASE P4 WHEN '' THEN Field1 Else @P4 END

and Field5 = CASE P5 WHEN '' THEN Field1 Else @P5 END

and Field6 = CASE P6 WHEN '' THEN Field1 Else @P6 END

Order By MyIndexedField

If P1 is an empty string, then we get "Field1 = Field1" and there is no filtering. Otherwise, we filter to check Field1 = @P1. If Field1 has null values, however, the equality compare doesn't work. The query can be modified to handle this, it's just a little messier.

Hope this helps,

Scott Thornburg

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

See if this helps: http://www.sommarskog.se/dyn-search.html

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

Vladan SSC-Insane Points: 22072 More actions · Answer 4

One way to do this is to construct the where clause so that it always checks whether the parameter is NULL or not, and acts accordingly.

WHERE

(@p1 IS NULL OR (@p1 IS NOT NULL AND tbl.col1 = @p1)) AND

(@p2 IS NULL OR @p3 IS NULL OR (@p2 IS NOT NULL AND @p3 IS NOT NULL AND tbl.col2 BETWEEN @p2 AND @p3)) AND /*need always both boundaries to compare*/

(@p4 IS NULL OR (@p4 IS NOT NULL AND tbl.col4 > @p4)) AND

.....

However, be sure to visit the site Frank pointed to, there are lots of things you can use. I've learned a lot from there

Vladan