[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simplify T-SQL stored procedure

Posted on 2011-04-25
4
Medium Priority
?
245 Views
Last Modified: 2012-05-11
Hello All,

I have modified the exisiting stored procedure to add the user security code. The last two INNER JOINS (bb and cc tables) are added. The rest of the code is old code.

But the tables are being used twice in the same query. There are two sub-queries are for SELECT which are using the same tables as the INNER JOIN in the main query are.

Is there any qay I could simplify the code to eliminate repetitive calls to the same tables.

Thanks,
-B


SELECT 
	aa.col1
	,(SELECT CountryDesc
    FROM Countries   
    WHERE CountryCode = aa.Country) As CountryDesc  
   ,aa.col2
   ,(SELECT top 1 a.PrimID  
    FROM tbl1 a  
    INNER JOIN tbl2 b ON b.StudyID = a.StudyID   
     AND a.LocID = b.LocID  
    INNER JOIN tbl3 c ON c.ID = a.StudyID   
     AND c.LocID = a.LocID  
    WHERE LocName = @Location
     AND a.CountryCode = aa.Country) As PrimID 
   ,aa.col3
   ,aa.col4 
  FROM tbl aa
  INNER JOIN tbl2 bb ON bb.StudyID = aa.StudyID   
     AND aa.LocID = bb.LocID  
  INNER JOIN tbl3 cc ON cc.ID = aa.StudyID   
     AND cc.LocID = aa.LocID
  WHERE aa.LocName = @Location AND cc.UserID = @UserID

Open in new window

0
Comment
Question by:BrookK
  • 2
  • 2
4 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35459866
-- assuming "tbl" and "tbl1" are the same table:

SELECT aa.col1
      ,CN.CountryDesc
      ,aa.col2
      ,aa.PrimID
      ,aa.col3
      ,aa.col4
  FROM tbl aa
  JOIN tbl2 bb
    ON bb.StudyID = aa.StudyID  
   AND aa.LocID = bb.LocID  
  JOIN tbl3 cc
    ON cc.ID = aa.StudyID  
   AND cc.LocID = aa.LocID
  JOIN Contries CN
    on CN.CountryCode = aa.Country
 WHERE aa.LocName = @Location
   AND cc.UserID = @UserID



-- assuming "tbl" and "tbl1" are NOT the same table:

SELECT aa.col1
      ,CN.CountryDesc
      ,aa.col2
      ,aa.PrimID

      ,(SELECT top 1 a.PrimID  
          FROM tbl1 a  
          JOIN tbl2 b
            ON b.StudyID = a.StudyID  
           AND a.LocID = b.LocID  
          JOIN tbl3 c
            ON c.ID = a.StudyID  
           AND c.LocID = a.LocID  
         WHERE LocName = @Location
           AND a.CountryCode = aa.Country) As PrimID

      ,aa.col3
      ,aa.col4
  FROM tbl  aa
  JOIN tbl2 bb
    ON bb.StudyID = aa.StudyID  
   AND bb.LocID = aa.LocID  
  JOIN tbl3 cc
    ON cc.ID = aa.StudyID  
   AND cc.LocID = aa.LocID
  JOIN tbl1 XX
    ON bb.StudyID = XX.StudyID  
   AND bb.LocID = XX.LocID  
  JOIN Contries CN
    on CN.CountryCode = aa.Country
 WHERE aa.LocName = @Location
   AND cc.UserID = @UserID
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 35459870
ooops, the second query above should be:


SELECT aa.col1
      ,CN.CountryDesc
      ,aa.col2
      ,xx.PrimID
      ,aa.col3
      ,aa.col4
  FROM tbl  aa
  JOIN tbl2 bb
    ON bb.StudyID = aa.StudyID  
   AND bb.LocID = aa.LocID  
  JOIN tbl3 cc
    ON cc.ID = aa.StudyID  
   AND cc.LocID = aa.LocID
  JOIN tbl1 XX
    ON bb.StudyID = XX.StudyID  
   AND bb.LocID = XX.LocID  
  JOIN Contries CN
    on CN.CountryCode = aa.Country
 WHERE aa.LocName = @Location
   AND cc.UserID = @UserID
0
 

Author Comment

by:BrookK
ID: 35460311
Soory for the confusion. tbl1 and tbl  are   different tables.
0
 

Author Closing Comment

by:BrookK
ID: 35461274
Perfect solution
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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 …
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

867 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