SQL Stored Proc - multiple conditions

Hi Experts

I am having a trouble with a SQL Stored Proc where I need to test for various conditions before searching for records.

the code below all works, however I want to add a new condition to it to see if two fields are null rather than just one.

This is the working code

alter procedure SupplierOrders.GetLinesConditions_SP

@SupplierOrderNumber varchar(64),
@WorkOrderNumber int,
@OrderNumber int

as 
begin
if @WorkOrderNumber is null
select 
o.OrderNumber, o.WorkOrderNumber, 
s.SupplierName, 
o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate,
oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost, 
oi.received
from SupplierOrders.Orders o
left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID 
left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber
where 
o.SupplierOrderNumber = @SupplierOrderNumber and
--o.WorkOrderNumber = @WorkOrderNumber --and
o.OrderNumber = @OrderNumber 

else

if @SupplierOrderNumber  is null
select 
o.OrderNumber, o.WorkOrderNumber, 
s.SupplierName, 
o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate,
oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost, 
oi.received
from SupplierOrders.Orders o
left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID 
left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber
where 
--o.SupplierOrderNumber = @SupplierOrderNumber and
o.WorkOrderNumber = @WorkOrderNumber and
o.OrderNumber = @OrderNumber 

else


if @OrderNumber is null
select 
o.OrderNumber, o.WorkOrderNumber, 
s.SupplierName, 
o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate,
oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost, 
oi.received
from SupplierOrders.Orders o
left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID 
left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber
where 
o.SupplierOrderNumber = @SupplierOrderNumber and
o.WorkOrderNumber = @WorkOrderNumber --and
--o.OrderNumber = @OrderNumber 

else


if @OrderNumber is null and @WorkOrderNumber is null
select 
o.OrderNumber, o.WorkOrderNumber, 
s.SupplierName, 
o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate,
oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost, 
oi.received
from SupplierOrders.Orders o
left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID 
left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber
where 
o.SupplierOrderNumber = @SupplierOrderNumber --and
--o.WorkOrderNumber = @WorkOrderNumber --and
--o.OrderNumber = @OrderNumber 

else

select 
o.OrderNumber, o.WorkOrderNumber, 
s.SupplierName, 
o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate,
oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost, 
oi.received
from SupplierOrders.Orders o
left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID 
left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber--
where 
o.SupplierOrderNumber = @SupplierOrderNumber and
o.WorkOrderNumber = @WorkOrderNumber and
o.OrderNumber = @OrderNumber 
end
go

Open in new window


now, if I add this in and execute it is not returning any results.

if @OrderNumber is null and @WorkOrderNumber is null
select 
o.OrderNumber, o.WorkOrderNumber, 
s.SupplierName, 
o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate,
oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost, 
oi.received
from SupplierOrders.Orders o
left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID 
left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber
where 
o.SupplierOrderNumber = @SupplierOrderNumber

Open in new window


I would be grateful for any and all help on this one.

thanks

Simon
SimonPrice33Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim P.Connect With a Mentor Commented:
Try this idea on. The base SQL statement isn't going to change. So assign that statement to a variable. The Where statement is what's going to change. So test for all three input conditions and then use the correct where clause that fits the conditions. I've built the sample code suggestion. I'll let you get all the conditions built out on your own.

alter procedure SupplierOrders.GetLinesConditions_SP

@SupplierOrderNumber varchar(64),
@WorkOrderNumber int,
@OrderNumber int

as 
begin
declare @SQL			as	varchar(2000)
declare @WhereClause	as	varchar(500)

select	@SQL = 'select ' +
			'o.OrderNumber, o.WorkOrderNumber,  ' +
			's.SupplierName,  ' +
			'o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate, ' +
			'oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost,  ' +
			'oi.received ' +
			'from SupplierOrders.Orders o ' +
			'left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID  ' +
			'left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber '

if @WorkOrderNumber is null and @SupplierOrderNumber  is not null
begin
select	@WhereClause = 'where ' +
		'o.SupplierOrderNumber = @SupplierOrderNumber and ' +
		'o.OrderNumber = @OrderNumber '
end


if @SupplierOrderNumber  is null and @WorkOrderNumber is not null and @OrderNumber is not null
begin 
select	@WhereClause = 'where ' + 
		'o.WorkOrderNumber = @WorkOrderNumber and ' + 
		'o.OrderNumber = @OrderNumber '
end

if @OrderNumber is null and @SupplierOrderNumber is not null and @WorkOrderNumber is not null
begin 
select	@WhereClause = 'where ' + 
		'o.SupplierOrderNumber = @SupplierOrderNumber and ' + 
		'o.WorkOrderNumber = @WorkOrderNumber'
end

exec @SQL + @WhereClause

end
go

Open in new window

0
 
sammySeltzerCommented:
What happens if you put them in your WHERE predicate?

where SupplierOrderNumber IS NULL

OR WorkOrderNumber IS NULL

OR OrderNumber IS NULL

AND o.SupplierOrderNumber = @SupplierOrderNumber

Open in new window

0
 
SimonPrice33Author Commented:
im not looking for the field with null value, each field should have a value that is being search, what I am trying to achieve is passing parameters with null values to give multiple search options.

so

if I were searching and all I had was the supplier order number from their invoice it wouldn't matter that I don't have my own internal order number of the work order that is associated with because the results would come up based on the Supplier Order number.

and when I have this down and working it will be the same if I don't have the supplier order number, but only my internal order number then I can get the details then too..

I want to make this as easy and user friendly so that the user doesn't have to know everything to get anything

thanks

SImon
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sammySeltzerCommented:
If I understand correctly if you have supplier order number, you get the information you are after.

If, however, you don't have supplier order number, your own order number will work just the same as supplier order number?

If so, maybe you can try UNION
select 
IsNull(o.OrderNumber,o.SupplierOrderNumber ), o.WorkOrderNumber, 
s.SupplierName, 
o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate,
oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost, 
oi.received
from SupplierOrders.Orders o
left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID 
left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber
where 
o.SupplierOrderNumber = @SupplierOrderNumber 

UNION

select 
IsNull(o. o.WorkOrderNumber,o.SupplierOrderNumber ), 
s.SupplierName, 
o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate,
oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost, 
oi.received
from SupplierOrders.Orders o
left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID 
left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber
where 
o.SupplierOrderNumber = @SupplierOrderNumber 

Open in new window


This way, if either ordernumber or workordernumber is null, they get assigned the value of supplierOrderNumber and you can then get results by passing value to the param.

Try and see how this goes
0
 
jkanisCommented:
Why not just use a case statement inside a single Select Statement.  When the value is NULL pass a "True" else use the value as a condition.  The only issue I see is if all three values are NULL, you are basically selecting without any "WHERE" clause, maybe perform one check at beginning to see if all 3 values are true and return something else... unless that's what you want?


select 
o.OrderNumber, o.WorkOrderNumber, 
s.SupplierName, 
o.SupplierOrderNumber, o.OrderDate, o.DueDeliveryDate,
oi.item, oi.qty,oi.PriceatOrder, oi.itemtotalcost, 
oi.received
from SupplierOrders.Orders o
left join SupplierOrders.Suppliers s on s.SupplierID = o.SupplierID 
left join SupplierOrders.Order_Item oi on oi.OrderNumber = o.OrderNumber
where 
CASE WHEN @SupplierOrderNumber IS NULL THEN TRUE ELSE 
o.SupplierOrderNumber = @SupplierOrderNumber 
END
and
CASE WHEN @WorkOrderNumber IS NULL THEN TRUE ELSE 
o.WorkOrderNumber = @WorkOrderNumber 
END
and
CASE WHEN @OrderNumber IS NULL THEN TRUE ELSE 
o.OrderNumber = @OrderNumber 
END

Open in new window

0
 
SimonPrice33Author Commented:
Hi Guys,

Thanks for your comments I will try the suggestions out later today and let you know how I get on.

Both look as if they could work for me so thank you very much.
0
 
SimonPrice33Author Commented:
Jim P,

I get a syntax error when trying to run this code at the end where it is executed.

exec @SQL + @WhereClause

is it the + (plus) that causes the issue
0
 
SimonPrice33Author Commented:
JKanis, I think you might be on to something but your SQL is wrong... I will have a play with this and let you now how I get on
0
 
Jim P.Commented:
I always forget about that. You need parentheses. Try it as:

exec (@SQL + @WhereClause)
0
 
SimonPrice33Author Commented:
Ive done it now... I went back to my original code and rewrote it and this time it worked.. must have been something I did \ didn't do...

will try that anyway and then award the points if it works.  :)
0
 
SimonPrice33Author Commented:
although am going with what I did yesterday for now as I have this working as I originally wanted, this works and is the accepted solution and I may come back to it sometime soon or later in the future when im stuck.

thank you :)
0
All Courses

From novice to tech pro — start learning today.