WHERE clause as variable in StoredProc 3

[devRyan]

Hi all,

I'm currently building a search engine function for a site I'm working on. The search form has 6 inputs that will serve as variables in my SQL statement(ie; userName, country, region, etc.). Any combination of these can be chosen at a time.

In order to not have to build a method and stored procedure for every combination of these 6 fields, I'd like to build my WHERE clause in my form processing page, then send the string to a storedProc as an input variable.

Something like this:

CREATE PROCEDURE spStoredProc
(
	@WhereString ntext
)
AS
SET NOCOUNT ON
SELECT * FROM <dataTable> + @WhereString

SQLServer(2000) doesn't like this syntax, so I'm looking for the correct syntax for doing this, or possibly another method of achieve this same goal. Also, I don't want to have to create my (entire)SQL string in the frontend and end up having to send it in the command.

As my statement is above, it will work whether all variables are submitted or none at all. I just can't figure out how to read the @WhereString as part of the SELECT statement.

Thanks,

Ryan

 

[AlexCuse]

Wouldn't this make more sense?

CREATE PROCEDURE spStoredProc
(
    @Parameter ntext
)
AS
SET NOCOUNT ON
SELECT * FROM [b]myTable[/b]
WHERE SomeColumn = @Parameter

You should really use varchar for your parameter though, I can't imagine you are sending > 8000 characters in!

What you are trying to do is called dynamic SQL, and it sucks for a variety of reasons. Odds are that is NOT what you want to do, but I encourage you to do a google search on the subject if you think it would be good (there are times when it's the only way).

Hope this hlelps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

NTEXT!!!!!!!!!!!!!!!
WOW!
What is that WHERE clause?

But if you consider using varchar(8000) instead:

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] spStoredProc
(
    @WhereString [COLOR=blue]varchar[/color](8000)
)
[COLOR=blue]AS[/color]
[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]
[COLOR=blue]DECLARE[/color] @lcSQL [COLOR=blue]varchar[/color](8000)
[COLOR=blue]SET[/color] @lcSQL = [COLOR=red]'SELECT * FROM <dataTable> '[/color] + @WhereString
[COLOR=blue]EXEC[/color] (@lcSQL)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[devRyan]

ntext, I know, it was just a mock representation of the string. I plan on using a much smaller varchar.

Aside from that.

Boris, that worked, or at least it allowed the creation of the sp.

Alex, I'll read up on dynamic SQL as you mentioned, it may not really be the best practice, though this is what I want to do. I suppose I'll find out with further reading.

As far as simply using a parameter for each variable, that wouldn't work for what I'm doing. If I did that then the variables would be hardcoded into my SQL statement. In such a case, if a field in my form was not selected then the parameter would be passed/initialized as an empty string, which would bring back the wrong set of results.

Ex. If the userName is not selected in the form;

With my method: the userName parameter would not be used as a filter in the WHERE clause, thus not affecting the query results.

With individual params: the userName parameter would be passed as an empty string, as a filter in the WHERE clause, thus returning only rows that have a userName that = '', which if I've done everything correctly would return 0 results, that would be bad.

Thanks for the help guys. I'll let you know how it runs was I get it rolling.

Ryan

 

[devRyan]

I suppose I could check to see if parameters are sent as empty strings in my sp. I'll look into doing that, so I don't have any of my SQL generated outside of SQL Server.

hmmm....

 

[gmmastros]

Ryan,

There is still a way to do it. Let me show you....

[COLOR=blue]Create[/color] [COLOR=blue]Procedure[/color] Search
    @UserName [COLOR=blue]Varchar[/color](50),
    @EyeColor [COLOR=blue]VarChar[/color](20),
    @ShoeSize [COLOR=blue]int[/color]
[COLOR=blue]As[/color]
[COLOR=blue]Set[/color] [COLOR=#FF00FF]NoCount[/color] [COLOR=blue]On[/color]

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   [COLOR=blue]Table[/color]
[COLOR=blue]Where[/color]  (@UserName = [COLOR=red]''[/color] Or UserName = @UserName)
       And (@EyeColor = [COLOR=red]''[/color] Or EyeColor = @EyeColor)
       And (@ShoeSize = 0 Or ShoeSize = @ShoeSize)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

You could use a CASE in the WHERE: (let's see if I can get the syntax correct)

WHERE
  CASE WHEN @UserName IS NULL
    THEN UserName = UserName
    ELSE UserName = @UserName
  CASE ......

What happens is that if the parameter is NULL, it will return everything from that column. If it is not NULL then it will return only what matches the inputted parameter. If you go this route, set every parameter to the default of NULL.

-SQLBill

Posting advice: FAQ481-4875

 

[bborissov]

Ryan,
As George said everything could be done (or almost everything)
Just show us what you want to do. Dynamic SQL is not the bes choice.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

>> (let's see if I can get the syntax correct)

Nope. Try again.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Here's an example with some sample data.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](UserName [COLOR=blue]VarCHar[/color](100), EyeColor [COLOR=blue]VarChar[/color](100), ShoeSize [COLOR=blue]int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'devRyan'[/color], [COLOR=red]'blue'[/color], 10)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'AlexCuse'[/color], null, 10)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'bborissov'[/color], [COLOR=red]'blue'[/color], NULL)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'gmmastros'[/color], [COLOR=red]'red'[/color], 9)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'SQLBill'[/color], [COLOR=red]''[/color], 8)

[COLOR=blue]Declare[/color] @UserName [COLOR=blue]VarChar[/color](100)
[COLOR=blue]Declare[/color] @EyeColor [COLOR=blue]VarChar[/color](100)
[COLOR=blue]Declare[/color] @ShoeSize [COLOR=blue]Int[/color]

[COLOR=green]-- Try changing the values here.
[/color][COLOR=blue]Set[/color] @UserName = [COLOR=red]''[/color]
[COLOR=blue]Set[/color] @EyeColor = [COLOR=red]''[/color]
[COLOR=blue]Set[/color] @ShoeSize = 0

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  (@UserName = [COLOR=red]''[/color] Or UserName = @UserName)
       And (@EyeColor = [COLOR=red]''[/color] Or EyeColor = @EyeColor)
       And (@ShoeSize = 0 Or ShoeSize = @ShoeSize)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[devRyan]

Thanks for all the suggestions guys, but none seem to hit my need on the head. I think I've decided that using the parameter method would be best, since it would generate all of my SQL in the SP.

Something like:

@UserName varchar(50),
@ShoeSize int,
@EyeColor varhar(50)

AS
SET NOCOUNT ON

DECLARE @lcSQL varchar(2000)
DECLARE @hasWhere bit

SET @hasWhere = false

SET @lcSQL = 'SELECT * FROM DataTable '

if @UserName <> ''
  set @lcSQL += 'WHERE UserName = @UserName '
  set @hasWhere = true
end if

if @ShoeSize <> 0
  if @hasWhere
    set @lcSQL += 'AND ShoeSize = @ShoeSize '
  else
   set @lcSQL += 'WHERE ShoeSize = @ShoeSize '
  set @hasWhere = true
end if

if @EyeColor<> ''
  if @hasWhere
    set @lcSQL += 'AND EyeColor = @EyeColor'
  else
   set @lcSQL += 'WHERE EyeColor = @EyeColor'
  set @hasWhere = true
end if

-- at this point, I should have a complete queryString that contains only items in the WHERE clause that actually contain valid search criteria values, such as @UserName = 'Ryan'; conversely, if eyeColor is not selected in the form, it will be passed into the SP as a base value '', in which case the eyeColor portion of the Where clause is not appended to the @lcSQL string. --

EXEC(@lcSQL)

In the case that one of these is not chosen on the search form, I don't want it even used in the equation. If the userName is not chosen, that doesn't mean I want to search by UserName = '', it means that I don't want UserName to be a factor in the search.

In my data access object classes, I have all of my attributes initialized with base values in my constructors, thus strings = '' / ints = 0, these base values will persist through to my SP, which is why I don't search by IS NULL, in the above code.

Again, thank you all for helping figure this out.

Ryan

 

[devRyan]

George, how do you get the sql keywords colors to display like that?

 

[AlexCuse]

Ryan - have a look at setting default values to your parameters, or as George has shown passing an empty string to the parameters if nothing is supplied. A where clause like this:

where (@something = '' or something = @something)

will actually return everything from the table whenever @something = '' (because its' not really filtering based on anything in the table, but a parameter value). If it is <> '', THEN the filter is applied to the table. Its a neat trick, once you get your head around it.

As far as the SQL Keywords thing, have a look at this thread (the program is awesome!) thread183-1350140

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[devRyan]

Ok, so let me get this right, you're saying that the following statements are functionally equivalent(considering that #2 were actually in the right syntax)?

I'm just making sure, I've never seen the notation used in #1.

1) WHERE (@UserName = '' Or UserName = @UserName)

==

2) if @UserName <> ''
set @lcSQL = 'WHERE UserName = @UserName '
end if

Thanks,
Ryan

 

[devRyan]

Ok, now I feel dense.

I went back and ran the code the George posted and see how it works. That's exactly what I wanted.

 

[devRyan]

So I'm assuming the

(@UserName = '' OR UserName = @UserName)

is doing something like writing an if statement like

(@UserName = '' ? <do nothing> : UserName = @UserName)

?

Think maybe this would make a good FAQ topic?

 

[AlexCuse]

Ryan - the two you posted will get you the same end result, but they are not equivalent. The first will get you your desired result without the use of dynamic SQL, which will allow SQL to work much more efficiently.

The best way (for me) to think about it is as 2 separate where clauses (with only one necessarily being true, hence the OR).

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom