Simple query; Multiple Parameters

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 2005 Forums

Transact-SQL (2005)

Simple query; Multiple Parameters

Author

Topic

terbs
Starting Member

29 Posts

Posted - 2007-04-26 : 22:57:30

I am trying to construct a query which allows me to enter various parameters for one field(which works), but now I want to add further parameters to a different field, and this is where I am coming unstuck;

Heres what I have;

ALTER PROCEDURE dbo.sproc_GetJobs 

	@State1 int,
	@State2 int,
	@State3 int,
	@Customer varchar
	
AS

SELECT *
FROM TABjob

WHERE 
(State = @State1 OR
State = @State2 OR
State = @State3)
AND
Customer = @Customer


RETURN

The query executes, but it doesnt return results specific to the Customer parameter, any help would be great

cheers

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-26 : 23:23:15

Your WHERE clause looks fine to me.
Are you sure there isn't a problem with your data or with the parameters you are testing?
Post the DDL for the table, along with some sample data.

e4 d5 xd5 Nf6

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-27 : 00:40:58

It will work fine and also check u have given correct input..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 01:20:22

Do you have values for all integer parameters? Are some of them NULL?

ALTER PROCEDURE dbo.sproc_GetJobs 
(
	@State1 int,
	@State2 int,
	@State3 int,
	@Customer varchar
)	
AS

SET NOCOUNT ON

SELECT  *
FROM    TABjob
WHERE   State IN (@State1, @State2, @State3)
AND     Customer = @Customer

Peter Larsson
Helsingborg, Sweden

terbs
Starting Member

29 Posts

Posted - 2007-04-27 : 02:31:47

discovered the problem, appreciate the help all

did not have "(10)" after varchar, very silly

cheers

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-27 : 11:41:13

Dang! I should have caught that one, having made that mistake myself innumerable times.

e4 d5 xd5 Nf6

terbs
Starting Member

29 Posts

Posted - 2007-04-29 : 20:51:15

more problems!

if NULL is entered for the parameter @Customer, the procedure doesnt work. How do I get around this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-29 : 21:09:54

[code]
SELECT *
FROM TABjob
WHERE State in (@State1, @State2, @State3)
AND Customer = coalesce(@Customer, Customer)
[/code]

terbs
Starting Member

29 Posts

Posted - 2007-04-29 : 21:20:40

thanks khtan... right on the money

terbs
Starting Member

29 Posts

Posted - 2007-04-30 : 00:20:32

haha this had ended up being a step by step tutorial

ive add anouther field "status", which works pretty much like state. I.e Adding multiple parameters into the same field, or none at all. The parameters are things like "Entered" "Cancelled" or "Completed" etc. The procedure executes but it doesnt specify correctly. E.g I enter "Completed" and I get Cancelled Entered and Completed etc returned. Ive tried with just a single parameter been passed in which works, but the method ive used for multiple parameters here is excatly the same as state, which works. any help?

[CODE]ALTER PROCEDURE dbo.sproc_GetServiceCalls
(
@State1 int,
@State2 int,
@State3 int,
@Customer varchar(10),
@Catagory int,
@COMPANY varchar(50),
@CentralSite varchar(50),
@Status1 varchar(50),
@Status2 varchar(50),
@Status3 varchar(50)

)

AS

SET NOCOUNT ON

SELECT CallNumber, Status, State, Customer, Catagory, COMPANY.COMPANY

FROM TABservice, COMPANY

WHERE

(
State = coalesce(@State1,State) OR
State = coalesce(@State2,State) OR
State = coalesce(@State3,State)
)

AND

(
Status = coalesce(@Status1, Status) OR
Status = coalesce(@Status2, Status) OR
Status = coalesce(@Status3, Status)
)

AND

Customer = coalesce(@Customer, Customer)

AND

Catagory = coalesce(@Catagory, Catagory)

AND

COMPANY = coalesce(@COMPANY, COMPANY)

AND

COMPANY = coalesce(@CentralSite, COMPANY)

RETURN
[/CODE]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-30 : 01:58:20

COMPANY = @COMPANY and @CentralSite ?


AND
COMPANY = coalesce(@COMPANY, COMPANY)
AND
COMPANY = coalesce(@CentralSite, COMPANY)

terbs
Starting Member

29 Posts

Posted - 2007-04-30 : 02:16:30

yeah.. eventually the parameters being passed into this stored procedure come from dropdownlists on a ASPX page.

The central site dropdownlist has a query which only displays customers who are a central site, if you catch my drift...

terbs
Starting Member

29 Posts

Posted - 2007-05-01 : 01:53:06

khtam/anybody else...

is there an alternative to using

[CODE][FieldName] = COALESCE([@Parameter], [FieldName])[/CODE]

so that null values can be passed in without messing with the query?

im about 90% sure this is what is causing me troubles when im passing in multiple parameters to the same field..

(Ive cut down the stored procedure to the minimum basics, no cross tables etc, and the coalesce seems to be the catalyst)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 02:02:37

Do you have NULL data value in the column ?

the COALESCE([@Parameter], [FieldName]) is equivalent to ISNULL(@Paremeter, [FieldName])
or same as (use this if you have NULL data value in column FieldName)


(
    @Parameter IS NULL
OR  [FieldName] = @Parameter
)

terbs
Starting Member

29 Posts

Posted - 2007-05-01 : 02:37:36

do you mean are there null values in the database from that specific field?(Customer, Status)

(Yes there are.)

Or am I passing in null values into the parameters?

(Yes, depending on the situation.)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 02:38:56

quote:
Originally posted by terbs

do you mean are there null values in the database from that specific field?(Customer, Status)

(Yes there are.)

Or am I passing in null values into the parameters?

(Yes, depending on the situation.)

use this method


(
    @Parameter IS NULL
OR  [FieldName] = @Parameter
)

terbs
Starting Member

29 Posts

Posted - 2007-05-16 : 20:24:20

*bump*

hope your still around khtan;

how would I use the above method if I wanted to have more than one parameter?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-16 : 20:38:15

quote:
Originally posted by terbs

do you mean are there null values in the database from that specific field?(Customer, Status)

(Yes there are.)

Or am I passing in null values into the parameters?

(Yes, depending on the situation.)

Read this carefully:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/14/60133.aspx

Your specifications for "optional filter parameters" do not make logical sense if there are NULLS in your filter columns!

- Jeff
http://weblogs.sqlteam.com/JeffS

terbs
Starting Member

29 Posts

Posted - 2007-05-18 : 01:11:25

i have removed all the null values from my database.. should I now be able to perform the task?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources