Solved

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

Posted on 2016-08-11
6
61 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 49

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 49

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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