Solved

SQL Stored Proc - multiple conditions

Posted on 2014-03-15
11
336 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

867 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

25 Experts available now in Live!

Get 1:1 Help Now