Solved

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

Posted on 2016-08-11
6
54 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Backup Strategy 15 46
SQL DATEADD 10 71
reverse engineer .sql from php files 11 35
SQL Server Shrink hurting performance? 4 22
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 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