Simplify the stored proc

stephanel · July 25th, 2003, 07:50 AM

Is there a way to simplify the stored proc so it will used only one query?

This is what I have so far

IF (SELECT COUNT(DataID) FROM Data_Companies WHERE CompanyID = @CompanyID) > 0
BEGIN
    SELECT Data.DataID, Data.DataName, ISNULL(Data_Labels_Companies.Label, Data_Labels.Label)
    FROM Data_Companies INNER JOIN Data
            ON Data_Companies.DataID = Data.DataID
            AND Data_Companies.CompanyID = @CompanyID
        INNER JOIN Data_Labels
            ON Data.DataID = Data_Labels.DataID
            AND Data_Labels.LanguageID = @LanguageID
        LEFT OUTER JOIN Data_Labels_Companies
            ON Data_Labels.DataID = Data_Labels_Companies.DataID
            AND Data_Labels_Companies.LanguageID = @LanguageID
            AND Data_Labels_Companies.Company = @CompanyID
    ORDER BY Data_Companies.OrderSeq
END

ELSE
BEGIN
    SELECT Data.DataID, Data.DataName, ISNULL(Data_Labels_Companies.Label, Data_Labels.Label)
    FROM Data INNER JOIN Data_Labels
            ON Data.DataID = Data_Labels.DataID
            AND Data_Labels.LanguageID = @LanguageID
        LEFT OUTER JOIN Data_Labels_Companies
            ON Data_Labels.DataID = Data_Labels_Companies.DataID
            AND Data_Labels_Companies.LanguageID = @LanguageID
            AND Data_Labels_Companies.Company = @CompanyID
    ORDER BY Data.OrderSeq
END

StÃ©phane Lajoie

David Cameron · July 27th, 2003, 06:53 PM

The code below assumes that Data.OrderSeq and Data_Companies.OrderSeq are the same data type. If they aren't you could cast both to varChar.

Also you could probably avoid having to use the variable @Exists, but this looks cleaner to me.

Code:

DECLARE @Exists bit

SELECT @Exists = CAST(COUNT(DataID) AS bit)
FROM Data_Companies
WHERE CompanyID = @CompanyID

SELECT Data.DataID, Data.DataName,
    ISNULL(Data_Labels_Companies.Label, Data_Labels.Label)
FROM Data_Companies
    INNER JOIN Data    ON
    Data_Companies.DataID = Data.DataID
    AND Data_Companies.CompanyID = @CompanyID
    INNER JOIN Data_Labels ON
    Data.DataID = Data_Labels.DataID
    AND Data_Labels.LanguageID = @LanguageID
    LEFT OUTER JOIN Data_Labels_Companies ON
    Data_Labels.DataID = Data_Labels_Companies.DataID AND
    Data_Labels_Companies.LanguageID = @LanguageID AND
    Data_Labels_Companies.Company = @CompanyID
ORDER BY CASE @Exists
             WHEN 1 THEN Data_Companies.OrderSeq
             ELSE Data.OrderSeq
         END

regards
David Cameron

stephanel · August 5th, 2003, 10:14 AM

Thanks David for your response. You query is good but I think you miss understood what I want. I'll try to make this more clear to understand.

This is the definition of my table:

Data: DataID [int], DataName [varchar], OrderSeq [int]
Containts all the data for the application.

DataCompanies: DataID [int], CompanyID [int], OrderSeq [int]
Containts the custom data for a given company

DataLabels: DataID [int], LanguageID [int], Label [varchar]
Containts the label to display in for each language

DataLabelsCompanies: DataID [int], LanguageID [int], CompanyID [int], Label [varchar]
Containts the custom label for a given company

This is an exemple of what the table may containt:

Data
1,Mister,1
2,Misses,2
3,Doctor,4
4,Miss,3

DataCompanies
1,1,3
2,1,1
4,1,2
1,3,4
2,3,2
3,3,1
4,3,3

DataLabels
1,1,Mr
1,2,M
2,1,Mrs
2,2,Mdm
3,1,Dr
3,2,Doc
4,1,Ms
4,2,Mdl

DataLabels
4,1,3,Miss

If the @CompanyID is equal to 1 and LanguageID to 2, I should get
2,Misses,M
4,Miss,Mdl
1,Mister,M

If the @CompanyID is equal to 2 and LanguageID to 1, I should get
1,Mister,Mr
2,Misses,Mrs
4,Miss,Ms
3,Doctor,Dr

If the @CompanyID is equal to 3 and LanguageID to 1, I should get
3,Doctor,Dr
2,Misses,Mrs
4,Miss,Miss
1,Mister,Mr

Is there an other way to get this data from my stored proc.

StÃ©phane Lajoie

David Cameron · August 5th, 2003, 08:45 PM

You've confused me. I have to admit I don't understand your table structure. When I posted my reply as far as I could tell it would do exactly what your earlier query did, except that it did it without the IF. My understanding was that you wanted a dynamic where clause based on whether some rows existed.

On the tables, have you posted all the columns in these tables? Which ones are primary keys (including the composite keys)? Which ones are foreign keys?

regards
David Cameron