Solved

SQL Stored procedure - How do I do this simple query is one line?

Posted on 2016-08-11
6
58 Views
Last Modified: 2016-08-11
hi,

Is there a way of doing the following query in one line?

DECLARE @ShiptoCode		varchar(10)

SET @ShiptoCode = (Select TOP 1 Ship_To From MCRS_LU_Stores Where Store_Number = @Ship) 

IF (@ShiptoCode IS NOT NULL) OR (LEN(@ShiptoCode) > 0)
BEGIN
       SET @ShiptoCode = (Select TOP 1 Ship_To From MCRS_LU_Stores_Legacy Where Store_Number = @Ship)
END

Open in new window


So basically, look for a match in TABLE1 (MCRS_LU_STORES, if no match, check TABLE2 (MCRS_LU_Stores_Legacy).


Also - Is there a way to check if a stored procedure has been triggered?
0
Comment
Question by:SmashAndGrab
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 41751871
You could try using COALESCE OR ISNULL
e.g.
DECLARE @ShiptoCode		varchar(10)

SET @ShiptoCode = select COALESCE(
                                   (Select TOP 1 Ship_To From MCRS_LU_Stores Where Store_Number = @Ship) 
                                 , (Select TOP 1 Ship_To From MCRS_LU_Stores_Legacy Where Store_Number = @Ship)
                                 )

Open in new window

but really using TOP(n)  requires the use of an ORDER BY
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 41751890
>Is there a way of doing the following query in one line?
sure.
however, I would not try to do it in 1 line, for performance reasons.
let's presume the first table contains most of the values, then you actually don't need to look at the second table at all. with 1-liner, you will look at the second table in any case, wasting db time much more than the simple "if" will loose

I hence will not provide any "syntax" for this question, as any version shall be less efficient than what you have already
1
 

Author Closing Comment

by:SmashAndGrab
ID: 41751895
Thanks both.

Guy - thank you for your comment.  It actually made me think.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41751897
note that I prefer this syntax, and I presume your logic is incorrect, as you are looking into the "legacy" table only IF you actually have a record in the first table, hence I also did a second change to the syntax

DECLARE @ShiptoCode            varchar(10)
Select TOP 1 @ShiptoCode  = Ship_To From MCRS_LU_Stores Where Store_Number = @Ship

IF (@ShiptoCode IS NULL) OR (LEN(@ShiptoCode) = 0)
BEGIN
       Select TOP 1 @ShiptoCode  = Ship_To From MCRS_LU_Stores_Legacy Where Store_Number = @Ship
END
1
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41751920
Guy is quite right, making the code smaller does not make it more efficient (in this case)

But do note you need an order when using TOP(n)  if you want predictable/repeatable results
1
 

Author Comment

by:SmashAndGrab
ID: 41751931
Thanks all.  Some excellent comments here.

Really has given me something to think about.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Many to one in one row 2 46
Error in sql query statment. 21 69
T-SQL: Please describe what a page split is 5 57
Set a variable value in SQL Procedure 3 21
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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