Solved

SQL Stored Proc - multiple conditions

Posted on 2014-03-15
11
334 Views
Last Modified: 2014-03-18
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
0
Comment
Question by:SimonPrice33
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39931317
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
 

Author Comment

by:SimonPrice33
ID: 39931327
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
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39931468
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39931549
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
 

Expert Comment

by:jkanis
ID: 39933448
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:SimonPrice33
ID: 39933582
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
 

Author Comment

by:SimonPrice33
ID: 39937488
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
 

Author Comment

by:SimonPrice33
ID: 39937502
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39937548
I always forget about that. You need parentheses. Try it as:

exec (@SQL + @WhereClause)
0
 

Author Comment

by:SimonPrice33
ID: 39937733
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
 

Author Closing Comment

by:SimonPrice33
ID: 39938891
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now