asked on

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

knightEknight

-- 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

ASKER CERTIFIED SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrookK

ASKER

Soory for the confusion. tbl1 and tbl are different tables.

BrookK

ASKER

Perfect solution