Stored procedure question

[millertodd]

I'm still a bit new to MS SQL and was wondering if there was a better way to do what I want.

If the user supplies a location the LocID with be > 0 and I want to select the data where emp.LOCATION_ID = @LocID. If the LocID = 0 then I don't want to include that in the where clause.

I wrote something that works but seems like there should be an easier way to do this.

Any comments or suggestions are welcome.

Thanks!

CREATE PROCEDURE GET_REPORT
    -- Add the parameters for the stored procedure here
    @startDate datetime,
    @endDate datetime,
    @LocID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    IF ( @LocID = 0)
        BEGIN
            SELECT
            MAX(distinct qad.QA_DATA_ID), 
            CONVERT(VARCHAR,qad.DATA_DATE, 101) DATA_DATE, 
            SUM(qad.NEW_USERS) NEW_USERS, 
            SUM(qad.TRANSACTIONS),  
            SUM(qad.CORP_TRANSACTIONS), 
            SUM(qad.CALLS_QUEUE) CALLS, 
            SUM(qad.CORP_CALLS) CORP_CALLS, 
            SUM(qad.CALLS_DN) DN_CALLS,  
            SUM(qad.DN_TRANSFERS) DN_TRANS, 
            SUM(qad.CALLS_SHORT) SHORT_CALLS, 
            dbo.GET_CONVERSION (SUM(qad.TRANSACTIONS), 
                    SUM(qad.CORP_TRANSACTIONS),
                    (SELECT COUNT(TRANS_NUMBER) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4)AND 
                    (dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))), 
                    SUM(qad.CALLS_QUEUE),  
                    SUM(qad.CALLS_DN), 
                    SUM(qad.CORP_CALLS), 
                    SUM(qad.CALLS_SHORT), 
                    SUM(qad.DN_TRANSFERS)) ttlCon, 
            CONVERT(VARCHAR, SUM(qad.SALES_COBRAND), 1) SALES_COBRAND, 
            CONVERT(VARCHAR, SUM(qad.SALES_GOLD), 1) SALES_GOLD, 
            CONVERT(VARCHAR, SUM(qad.SALES_CORP), 1) SALES_CORP,  
            CONVERT(VARCHAR, (SELECT SUM(DOLLAR_AMOUNT) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4)AND 
            (dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))), 1) AS SALES_LS, 
            (SELECT COUNT(TRANS_NUMBER) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4)AND 
            (dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))) AS LS_TRANSACTIONS, 
            CONVERT(VARCHAR, dbo.TOTAL_TS( 
                SUM(qad.SALES_COBRAND),
                SUM(qad.SALES_GOLD), 
                SUM(qad.SALES_CORP),
                (SELECT SUM(DOLLAR_AMOUNT) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 )AND 
            (dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE)))), 1) TTS
            FROM QA_DATA qad JOIN EMPLOYEE emp 
                ON emp.EMPLOYEE_ID = qad.EMPLOYEE_ID 
        WHERE qad.DATA_DATE >= @startDate AND 
        qad.DATA_DATE <  @endDate AND 
        emp.REPORTABLE = 1 AND 
        emp.ENABLED > 0 AND 
        (dbo.IS_IN_DEPT(emp.DEPARTMENT_ID, 'inside sales') = 'true') 
        GROUP BY qad.DATA_DATE
 
        END
    ELSE
        BEGIN
 
            SELECT
            MAX(distinct qad.QA_DATA_ID), 
            CONVERT(VARCHAR,qad.DATA_DATE, 101) DATA_DATE, 
            SUM(qad.NEW_USERS) NEW_USERS, 
            SUM(qad.TRANSACTIONS),  
            SUM(qad.CORP_TRANSACTIONS), 
            SUM(qad.CALLS_QUEUE) CALLS, 
            SUM(qad.CORP_CALLS) CORP_CALLS, 
            SUM(qad.CALLS_DN) DN_CALLS,  
            SUM(qad.DN_TRANSFERS) DN_TRANS, 
            SUM(qad.CALLS_SHORT) SHORT_CALLS, 
            dbo.GET_CONVERSION (SUM(qad.TRANSACTIONS), 
                    SUM(qad.CORP_TRANSACTIONS),
                    (SELECT COUNT(TRANS_NUMBER) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 )AND 
                    (dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))), 
                    SUM(qad.CALLS_QUEUE),  
                    SUM(qad.CALLS_DN), 
                    SUM(qad.CORP_CALLS), 
                    SUM(qad.CALLS_SHORT), 
                    SUM(qad.DN_TRANSFERS)) ttlCon,
            CONVERT(VARCHAR, SUM(qad.SALES_COBRAND), 1) SALES_COBRAND, 
            CONVERT(VARCHAR, SUM(qad.SALES_GOLD), 1) SALES_GOLD, 
            CONVERT(VARCHAR, SUM(qad.SALES_CORP), 1) SALES_CORP,  
            CONVERT(VARCHAR, (SELECT SUM(DOLLAR_AMOUNT) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 OR tl.TRANSACTION_ID IS NULL)AND 
            (dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE)OR tl.TRANSACTION_ID IS NULL)), 1) AS SALES_LS, 
            (SELECT COUNT(TRANS_NUMBER) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 )AND 
            (dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE))) AS LS_TRANSACTIONS, 
            CONVERT(VARCHAR, dbo.TOTAL_TS( 
                SUM(qad.SALES_COBRAND),
                SUM(qad.SALES_GOLD), 
                SUM(qad.SALES_CORP),
                (SELECT SUM(DOLLAR_AMOUNT) FROM TRANSACTION_LOG tl WHERE (tl.TRANS_TYPE_ID = 4 )AND 
            (dbo.GetDateOnly(tl.TRANS_TIME) = dbo.GetDateOnly(qad.DATA_DATE)))), 1) TTS
            FROM QA_DATA qad JOIN EMPLOYEE emp 
                ON emp.EMPLOYEE_ID = qad.EMPLOYEE_ID 
            WHERE qad.DATA_DATE >= @startDate AND 
            qad.DATA_DATE <  @endDate AND 
            emp.REPORTABLE = 1 AND 
            emp.ENABLED > 0 AND 
            (dbo.IS_IN_DEPT(emp.DEPARTMENT_ID, 'inside sales') = 'true') AND
                emp.LOCATION_ID = @LocID
                GROUP BY qad.DATA_DATE
        END
END