SQL - 4 Variables?

NiCkNaMe

NiCkNaMe

NiCkNaMe

Soldato
Joined
17 Jul 2005
Posts
3,193
Hi Guys,

I'm pretty new when it comes to SQL and only know the basics.

I want a query that does the following.

I've got a page with four drop down boxes containing various variables. The default entry in the job down box is blank.

The user should be able to select one of the boxes, or more if they wish ... press the button which executes the SQL and returns the result.

Say I did an SQL query that;

select * from x where (var1 = @var1) AND (var2 = @var2) up to var 4

Can I pass a wildcard(?) in the =@ variable that I pass to the query if the user does not select anything in one of the boxes?

e.g...

select * from x where (var1 = *)

Hope i'm being clear :-S

Thanks
Steve
 
you can do
select * from x where var1 like @var1

and have the @var1 contain '%'
which should match anything.

However what about:

select * from x where field1=isnull(@var1,field1) and field2 = isnull(@var2,field2)
 
SimonCHere said:
you can do
select * from x where var1 like @var1

and have the @var1 contain '%'
which should match anything.
Click to expand...

That sounds like it would do the job perfectly... however when I try and preview the query inputting % I get...

Failed to convert parameter value from a string to an Int32. Is it a different wildcard for integers?
 
Like should convert int to varchar OK so
select * from table where [intfield] like '%'
should work.

I guess whatever you passing it to looks like:

Create PROCEDURE spDoStuff @Var1 As int

or

DECLARE @Var1 int

either way, change them to varchar(10) and you should be fine.
If not post up the code.

Code: 
declare @Var1 varchar(10)
set @Var1 ='%'

select top 10 * from bigtable where tableId like @Var1
 
Last edited:
(cast(user_id as varchar(8)) LIKE @user_id)

Allows me to use the % wildcard search by casting the int to a varchar and it works a treat.

If anyone can point out any flaws in the above that'd be great.

Thanks for the help Simon :)
 
I see. I'm not too concerned about the efficiency as it's not a massive database.

I guess an alternative would have been to create an if statement on the application side to choose to execute an appropriate query based upon the input.... although that would have meant creating four seperate queries in my table adapter.... ?

Thanks anyway, seems to be running as intended.
 
It sounds like you want something along the lines of:

Code: 
select *
from x
where (var1 = @var1 OR @var1 is null)
AND (var2 = @var2 OR @var2 is null) 
etc.
rather than using wildcards, unless I'm misinterpreting what you have said.

Either that or build the query dynamically based on what's selected in the boxes.

EDIT: Just seen what SimonCHere said in one of his posts is logically equivalent to what I've put.
 
Haircut said:
It sounds like you want something along the lines of:

Code: 
select *
from x
where (var1 = @var1 OR @var1 is null)
AND (var2 = @var2 OR @var2 is null) 
etc.
rather than using wildcards, unless I'm misinterpreting what you have said.
Click to expand...

This is precisely the approach I use, with this code embedded in a SProc.

Either that or build the query dynamically based on what's selected in the boxes.
Click to expand...

This solution precludes the use of Stored Procedures. Although there is a school of thought the advocates avoiding SProcs, they are at least as performant as any other technique and they are useful for securing the database (only allow DB interaction the SPs, plus resilient against SQL injection) - but this whole issue is an argument for another day.
 
You must log in or register to reply here.
Back
Top Bottom