Solved

SQL Stored Proc - multiple conditions

Posted on 2014-03-15
11
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 29

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 29

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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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