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

Transact-SQL (2005)

procedure parameters

Author

Topic

ComputerMike
Starting Member

18 Posts

Posted - 2009-06-03 : 12:08:04

Hi,

I have a table as so....

Customer Job
-------- ---
1 2
2 6
3 3
Null 4
Null 5

I have a procedure that is used in a Crystal Report as so.....

declare @BeginCustomer int = 1
declare @EndCustomer int = 6

select * from myTable
where Customer >= @BeginCustomer and <= @EndCustomer

The report is set up this way so the end user can select a range of Customers for the report, i.e. 1-2 and get just Customer 1 and 2. But if they leave blank it should return all records (all jobs), even where Customer is Null.

Any ideas or advice on how to write?

Thanks,

Mike

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 12:15:24

Maybe this:


select * from myTable
where (Customer >= @BeginCustomer OR @BeginCustomer is null)
  and (Customer <= @EndCustomer OR @EndCustomer is null)

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

ComputerMike
Starting Member

18 Posts

Posted - 2009-06-03 : 12:23:34

yes, but if I chose a range of 2-3 I still get Nulls records. I only want recrds for Customer 2 and 3 in this case.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 12:33:38

I think you err.
Lets say @BeginCustomer is set to 2 and @EndCustomer is set to 3
The WHERE would look like this:

where (Customer >= 2 OR @BeginCustomer is null) -- @BeginCustomer is null IS NOT TRUE
  and (Customer <= 3 OR @EndCustomer is null) -- @EndCustomer is null IS NOT TRUE

So it would bring what you want.
Am I right?

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

ComputerMike
Starting Member

18 Posts

Posted - 2009-06-03 : 12:52:13

No it's not working...here some code

declare @T1 table
(Customer int,
Job int)

INSERT INTO @T1 (Customer, Job) VALUES (1, 22)
INSERT INTO @T1 (Customer, Job) VALUES (2, 23)
INSERT INTO @T1 (Customer, Job) VALUES (3, 24)
INSERT INTO @T1 (Customer, Job) VALUES (4, 25)
INSERT INTO @T1 (Customer, Job) VALUES (Null, 26)
INSERT INTO @T1 (Customer, Job) VALUES (Null, 27)
INSERT INTO @T1 (Customer, Job) VALUES (Null, 28)

select * from @T1

declare @BeginCustomer int
set @BeginCustomer = 1 --Default value

declare @EndCustomer int
set @EndCustomer = 10 --Default value

select * from @T1
where (Customer >= @BeginCustomer OR @BeginCustomer is null)
and (Customer <= @EndCustomer OR @EndCustomer is null)

--Because EndUser did not change default, it should return Null records too
--Does not return Null records

--But if EndUser set Begin and EndCustomer
set @BeginCustomer = 2
set @EndCustomer = 3

--Only Customer 2 and 3 should be returned
select * from @T1
where (Customer >= @BeginCustomer OR @BeginCustomer is null)
and (Customer <= @EndCustomer OR @EndCustomer is null)
--works

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:08:47

whats the significance of default values 1 & 10? you could simply put default values as NULL and then it will work as you want with current logic

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 13:09:39

Sorry - are you serious?

You give a default other then NULL and your expected output should contain NULL values?

I have to reconsider this.

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

ComputerMike
Starting Member

18 Posts

Posted - 2009-06-03 : 13:19:35

Significance of default values is the range of customer numbers our DB allows. In the example above range of values would be 1 to 10. No other Customer numbers can exsist. the default values show up in the report, so the end user can leave alone and all customers are returned including Nulls, but they can change to select just some of the customers (2-3) and 2 and 3 are returned, but not nulls.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:30:34

which report tool are you using? sql reporting services?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 15:17:40

I don't like it but it works...


declare @T1 table
(Customer int,
Job int)

INSERT INTO @T1 (Customer, Job) VALUES (1, 22)
INSERT INTO @T1 (Customer, Job) VALUES (2, 23)
INSERT INTO @T1 (Customer, Job) VALUES (3, 24)
INSERT INTO @T1 (Customer, Job) VALUES (4, 25)
INSERT INTO @T1 (Customer, Job) VALUES (Null, 26)
INSERT INTO @T1 (Customer, Job) VALUES (Null, 27)
INSERT INTO @T1 (Customer, Job) VALUES (Null, 28)

--select * from @T1

declare @BeginCustomer int
set @BeginCustomer = 1 --Default value

declare @EndCustomer int
set @EndCustomer = 10 --Default value

declare @defaultbegin int 
set @defaultbegin= 1
declare @defaultend int
set @defaultend = 10

select * from @T1
where (Customer >= @BeginCustomer OR @BeginCustomer =@defaultbegin)
and (Customer <= @EndCustomer OR @EndCustomer =@defaultend)

--works


--But if EndUser set Begin and EndCustomer
set @BeginCustomer = 2
set @EndCustomer = 3

--Only Customer 2 and 3 should be returned
select * from @T1
where (Customer >= @BeginCustomer OR @BeginCustomer =@defaultbegin)
and (Customer <= @EndCustomer OR @EndCustomer =@defaultend)
--works

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources