incorrect where - 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 2008 Forums

Transact-SQL (2008)

incorrect where

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-05-28 : 06:12:11

/*
Hi,
There seems to be something wrong in the where clause.
As you see, the stored proc may have one, two or NO parameters.
But I do not get the correct data.
Any suggestions please?
Thanks
*/
alter procedure uspCustomerDetailsGet

@Client nvarchar(200) = null,
@FundNo int = 0

as

select
c.CustID,
c.CustomerName,
f.FundNo
from
Customers as c
inner join CustomerTimes as cs on c.CustID = cs.CustNo
left join Funds as f on cs.FundNo = f.FundNo
where
(c.CustomerName like '%' + @Client + '%' OR @Client is null)
OR (cs.FundNo = @FundNo OR cs.FundNo = 0)
group by
c.CustID,
c.CustomerName,
f.FundNo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 13:29:16

OR (cs.FundNo = @FundNo OR cs.FundNo = 0)

should be

OR (cs.FundNo = @FundNo OR ~~cs.FundNo~~ @FundNo = 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-05-29 : 05:33:08

Still with the new change you suggested the stored proc does not seem to return the correct data.
For example:
uspCustomerDetailsGet --> Returns data correctly
uspCustomerDetailsGet 'mike', 9 --> Returns data correctly
uspCustomerDetailsGet 'mike' --> DOES NOT return data correctly

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-29 : 05:49:28

Please give examples with data and expected output. We can't know what can see when you say NOT CORRECT.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-29 : 05:51:06

quote:
Originally posted by visakh16

OR (cs.FundNo = @FundNo OR cs.FundNo = 0)

should be

OR (cs.FundNo = @FundNo OR ~~cs.FundNo~~ @FundNo = 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maybe this

OR (cs.FundNo = @FundNo OR ~~cs.FundNo~~ @FundNo = 0)

should be

AND (cs.FundNo = @FundNo OR ~~cs.FundNo~~ @FundNo = 0)

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 12:12:05

quote:
Originally posted by arkiboys

Still with the new change you suggested the stored proc does not seem to return the correct data.
For example:
uspCustomerDetailsGet --> Returns data correctly
uspCustomerDetailsGet 'mike', 9 --> Returns data correctly
uspCustomerDetailsGet 'mike' --> DOES NOT return data correctly

now you can explain why it didnt work for you with sample data and expected output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-05-30 : 02:49:10

No sure why but passing parameter 'mike' returns the same number of records as if I did not pass any parameters.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:15:29

quote:
Originally posted by arkiboys

No sure why but passing parameter 'mike' returns the same number of records as if I did not pass any parameters.

did you change OR to AND as Fred suggested and try?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources