• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

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

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
SmashAndGrab
Asked:
SmashAndGrab
  • 2
  • 2
  • 2
2 Solutions
 
PortletPaulCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
SmashAndGrabAuthor Commented:
Thanks both.

Guy - thank you for your comment.  It actually made me think.
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
PortletPaulCommented:
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
 
SmashAndGrabAuthor Commented:
Thanks all.  Some excellent comments here.

Really has given me something to think about.
0

Featured Post

Identify and Prevent Potential Cyber-threats

Become the white hat who helps safeguard our interconnected world. Transform your career future by earning your MS in Cybersecurity. WGU’s MSCSIA degree program was designed in collaboration with national intelligence organizations and IT industry leaders.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now