Solved

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

Posted on 2016-08-11
6
42 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 142

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 142

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now