Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Stored Proc - multiple conditions

Posted on 2014-03-15
11
Medium Priority
?
345 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 2000 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

610 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