Passing Wildcard Parameters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Passing Wildcard Parameters

Author

Topic

Taragor
Starting Member

46 Posts

Posted - 2005-04-08 : 11:36:25

Hi everyone,

I'm having a newbie issue that I'm hoping someone can help with.

I'm currently creating an application in Visual Studio using VB .Net in conjunction with an SQL DB. The application needs to retrieve data based on a few parameters that are based off of dropdownlists within the application.

I can get everything working fine if the user specifies a value to each field but am unable to find a way (hope there's one) to pass a wildcard value as a parameter.

Within the dataadapter I have the following code:

Select *
From table
Where (Year = ?)

The application takes the year selection from the dropdownlist and passes it to sql as a parameter IE: 2000 or 2001 or 2002, etc.

What I'm trying to do with this though is also allow the user to select an ALL option that would return the results for all years.

Is there a way I can pass a wildcard parameter from my application that SQL will recognize as a wildcard and return all values?

I've also been experimenting with the LIKE function

Select *
From Table
Where (year like ?)

but this returns 0 records unless the user (or variable) is equal to an actual year.

Thank you in advance

Taragor

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-08 : 12:24:03

I am not into VB.Net but you can try this:

Select *
From Table
Where year = ? or 'ALL' = ?

The 'ALL' in this query is the value you are sending if the user selected the ALL option. I am also assuming that VB will substitute the same value from the drop down list to the 2 question marks above.

Billpl
Yak Posting Veteran

71 Posts

Posted - 2005-04-08 : 12:38:36

Sounds more like a job for an "IF" statement in your VB code. Technically you're trying to combine two different queries.

Taragor
Starting Member

46 Posts

Posted - 2005-04-08 : 13:42:55

quote:
Originally posted by rfrancisco

I am not into VB.Net but you can try this:

Select *
From Table
Where year = ? or 'ALL' = ?

The 'ALL' in this query is the value you are sending if the user selected the ALL option. I am also assuming that VB will substitute the same value from the drop down list to the 2 question marks above.

Not exactly. Assume the following:

dropdownlist has ALL,2000,2001,2002

Now the user can select a year (let's say 2000), when this happens I have a variable that is declared with the 2000 value which is passed through a parameterized select statement to the SQL DB. which launches the

select *
from table
where year = ? the ? at this point would be replaced with the value 2000 leading to the code reading

select *
from table
where year = 2000

If I force the value ALL to go to the select statement I'd get

select *
from table
where year = ALL

This would lead to no results since there is no such year value.

In essence I want to know what I can pass to SQL that it will recognize as a wildcard for all values

For instance using

select *
from table
where year = ?

I'm looking if there's a wildcard variable that sql would recognize so that it returns everything

in essence you'd get something like

select *
from table
where year = * where * is a wildcard that means return all values

Taragor

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-08 : 14:15:19

"Within the dataadapter I have the following code:

Select *
From table
Where (Year = ?)"

I would move that to a stored proc, and then call the stored proc.

Create Procedure dbo.FooBar
( @SentIn int)

AS

SET NOCOUNT ON

Select <column list>
From table <tablename>
Where Year =
CASE @SentIn WHEN 0 THEN Year ELSE @SentIn END

Taragor
Starting Member

46 Posts

Posted - 2005-04-08 : 14:19:26

DonAtWork: Wish I wouldn't be so congested from this darn head cold that I'd mentionned from the start that stored procedures are not an option for this project.

Taragor

Billpl
Yak Posting Veteran

71 Posts

Posted - 2005-04-08 : 16:24:55

No SP's? well, that's a bummer

Select *
from table
where (year=yearVar or yearVar = 'ALL')

yearVar = 'ALL' will return TRUE so the whole kitchen sink will be returned. rfrancisco had it right the first time.

Try taking Contact. Head colds and SQL are lousy mix...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-12 : 10:03:24

No SPs, yet you are using VS .NET and MS SQL? I simply dont understand. That is like giving you 2 boards, and a bunch of nails, and a hammer, and telling you to nail them togeather, but do NOT use the hammer. :/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources