Simplify T-SQL stored procedure

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

BrookKAsked:
Who is Participating?
 
knightEknightCommented:
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
 
knightEknightCommented:
-- 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
 
BrookKAuthor Commented:
Soory for the confusion. tbl1 and tbl  are   different tables.
0
 
BrookKAuthor Commented:
Perfect solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.