optimize the stored procedure

CREATE PROCEDURE emp_summary

@.emp_id int ,@.start_date datetime=0,@.end_date datetime=0

AS

SET NOCOUNT ON

IF @.start_date=0 AND @.end_date=0

BEGIN

SET @.end_date=getdate()

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date

END

ELSE

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date+1
GO

This is the Stored procedure i wrote to get the emp summary with date range and with no date ranges.If i pass start_date and end_Date Sp executes 'else' part if dont pass the parameters it execultes 'IF' part.Can i optimize this SP further?I'd use:CREATE PROCEDURE
@.emp_id INT
, @.start_date DATETIME = NULL
, @.end_date DATETIME = NULL
AS

SELECT *
FROM emp
WHERE emp_id = @.emp_id
AND join_date BETWEEN Coalesce(@.start_date, join_date) AND Coalesce(@.end_date, join_date)

RETURN
GOIf you can't afford the table scan, I'd resort to using dynamic SQL, but I'd try this first.

-PatP

Opinion on SQL Stored Procedure Syntax

I'm trying to improve the quality of life of this SP
CREATE Procedure p_generatedata @.StartDate smalldatetime, @.EndDate
smalldatetime
as
Set Nocount on
delete ttemp_data
Insert into ttemp_data
SELECT appeal_codes, appeal_type, appeal_code,
SUM(total_yes) AS total_yes, SUM(Pledge_Amount)
AS total_yes_amount, SUM(total_cc) total_cc,
SUM(total_cc_amount) AS total_cc_amount, SUM(last_gift)
AS last_gift_total, SUM(total_wc) AS total_wc, SUM(total_no)
AS total_no, SUM(call_resolution) AS total_resolves, leads
FROM (SELECT o.appeal_codes, o.appeal_code, o.appeal_type,
CASE WHEN o.call_resolution LIKE 1 THEN COUNT
(o.call_resolution)
END AS 'total_yes', SUM(o.Pledge_Amount)
AS Pledge_Amount,
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
COUNT(o.if_yes_pledge_or_credit_card)
END AS 'total_cc',
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
o.pledge_amount
END AS 'total_cc_amount', SUM(o.last_gift) AS last_gift,
CASE WHEN o.call_resolution LIKE 3 THEN
COUNT(o.call_resolution)
END AS 'total_wc',
CASE WHEN o.call_resolution LIKE 2 THEN
COUNT(o.call_resolution)
END AS 'total_no', COUNT(o.call_resolution)
AS call_resolution, c.lead as 'leads'
FROM tlead_sheets AS o INNER JOIN tappeal_codes AS c ON
o.appeal_codes = c.appeal_code
GROUP BY o.appeal_codes, o.appeal_code, o.call_resolution,
o.if_yes_pledge_or_credit_card, o.pledge_amount,
o.date_fld, o.last_gift, o.appeal_type, c.lead
HAVING o.date_fld BETWEEN @.StartDate AND @.EndDate)
AS tbl
GROUP BY appeal_codes, appeal_code, appeal_type, leads
Thanks for the helpWhat does "quality of life" mean? Do you want it to be happier? Look
prettier? Make more money? Be more efficient? All of the above? None of
the above?
<vncntj@.hotmail.com> wrote in message
news:1148417269.627636.34530@.u72g2000cwu.googlegroups.com...
> I'm trying to improve the quality of life of this SP
> CREATE Procedure p_generatedata @.StartDate smalldatetime, @.EndDate
> smalldatetime
> as
> Set Nocount on
> delete ttemp_data
> Insert into ttemp_data
> SELECT appeal_codes, appeal_type, appeal_code,
> SUM(total_yes) AS total_yes, SUM(Pledge_Amount)
> AS total_yes_amount, SUM(total_cc) total_cc,
> SUM(total_cc_amount) AS total_cc_amount, SUM(last_gift)
> AS last_gift_total, SUM(total_wc) AS total_wc, SUM(total_no)
> AS total_no, SUM(call_resolution) AS total_resolves, leads
> FROM (SELECT o.appeal_codes, o.appeal_code, o.appeal_type,
> CASE WHEN o.call_resolution LIKE 1 THEN COUNT
> (o.call_resolution)
> END AS 'total_yes', SUM(o.Pledge_Amount)
> AS Pledge_Amount,
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
> COUNT(o.if_yes_pledge_or_credit_card)
> END AS 'total_cc',
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
> o.pledge_amount
> END AS 'total_cc_amount', SUM(o.last_gift) AS last_gift,
> CASE WHEN o.call_resolution LIKE 3 THEN
> COUNT(o.call_resolution)
> END AS 'total_wc',
> CASE WHEN o.call_resolution LIKE 2 THEN
> COUNT(o.call_resolution)
> END AS 'total_no', COUNT(o.call_resolution)
> AS call_resolution, c.lead as 'leads'
> FROM tlead_sheets AS o INNER JOIN tappeal_codes AS c ON
> o.appeal_codes = c.appeal_code
> GROUP BY o.appeal_codes, o.appeal_code, o.call_resolution,
> o.if_yes_pledge_or_credit_card, o.pledge_amount,
> o.date_fld, o.last_gift, o.appeal_type, c.lead
> HAVING o.date_fld BETWEEN @.StartDate AND @.EndDate)
> AS tbl
> GROUP BY appeal_codes, appeal_code, appeal_type, leads
> Thanks for the help
>|||The assignment of colum aliases:
AS 'total_cc'
uses single quotes. Why use quotes? total_cc is a perfectly valid
column name, and anyway if a column name is in quotes it should be
"double quotes".
Looking at the construct:
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN COUNT(o.if_yes_pledge_or_credit_card)
END AS 'total_cc',
I am not sure that will work. The more common way to code this -
assuming I understand what it is supposed to return - is:
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN 1 ELSE 0
END) AS 'total_cc',
Looking at the test:
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
I forsee problems with the LIKE. LIKE is for strings, not numbers.
The HAVING test in the inner query looks like it should be a WHERE
test.
I suspect that the use of a derived table is avoidable. Take the
expression from the inner query that is SUMed in the outer:
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN o.pledge_amount
END AS 'total_cc_amount',
Consider
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN o.pledge_amount
ELSE 0
END AS total_cc_amount,
I reworked the whole thing into a single query that I THINK would do
what you are trying to do. I did not try to fix the LIKE tests and
such. It will be easier to read using a fixed pitch font.
SELECT o.appeal_codes,
o.appeal_type,
o.appeal_code,
SUM(CASE WHEN o.call_resolution LIKE 1
THEN COUNT (o.call_resolution)
ELSE 0
END) AS total_yes,
SUM(o.Pledge_Amount) AS total_yes_amount,
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN 1
ELSE 0
END) as total_cc,
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN o.pledge_amount
ELSE 0
END) AS total_cc_amount,
SUM(o.last_gift) AS last_gift_total,
SUM(CASE WHEN o.call_resolution LIKE 3
THEN 1
ELSE 0
END) AS total_wc,
SUM(CASE WHEN o.call_resolution LIKE 2
THEN 1
ELSE 0
END) AS total_no,
COUNT(o.call_resolution) AS total_resolves,
C.leads
FROM tlead_sheets AS o
JOIN tappeal_codes AS c
ON o.appeal_codes = c.appeal_code
WHERE o.date_fld BETWEEN @.StartDate AND @.EndDate
GROUP BY o.appeal_codes, o.appeal_code, o.appeal_type, c.leads
Hope that helps some.
Roy Harvey
Beacon Falls, CT
On 23 May 2006 13:47:49 -0700, vncntj@.hotmail.com wrote:

>I'm trying to improve the quality of life of this SP
>CREATE Procedure p_generatedata @.StartDate smalldatetime, @.EndDate
>smalldatetime
>as
>Set Nocount on
>delete ttemp_data
>Insert into ttemp_data
>SELECT appeal_codes, appeal_type, appeal_code,
> SUM(total_yes) AS total_yes, SUM(Pledge_Amount)
> AS total_yes_amount, SUM(total_cc) total_cc,
> SUM(total_cc_amount) AS total_cc_amount, SUM(last_gift)
> AS last_gift_total, SUM(total_wc) AS total_wc, SUM(total_no)
> AS total_no, SUM(call_resolution) AS total_resolves, leads
>FROM (SELECT o.appeal_codes, o.appeal_code, o.appeal_type,
> CASE WHEN o.call_resolution LIKE 1 THEN COUNT
>(o.call_resolution)
> END AS 'total_yes', SUM(o.Pledge_Amount)
> AS Pledge_Amount,
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
>COUNT(o.if_yes_pledge_or_credit_card)
> END AS 'total_cc',
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
>o.pledge_amount
> END AS 'total_cc_amount', SUM(o.last_gift) AS last_gift,
> CASE WHEN o.call_resolution LIKE 3 THEN
>COUNT(o.call_resolution)
> END AS 'total_wc',
> CASE WHEN o.call_resolution LIKE 2 THEN
>COUNT(o.call_resolution)
> END AS 'total_no', COUNT(o.call_resolution)
> AS call_resolution, c.lead as 'leads'
> FROM tlead_sheets AS o INNER JOIN tappeal_codes AS c ON
>o.appeal_codes = c.appeal_code
> GROUP BY o.appeal_codes, o.appeal_code, o.call_resolution,
>o.if_yes_pledge_or_credit_card, o.pledge_amount,
> o.date_fld, o.last_gift, o.appeal_type, c.lead
> HAVING o.date_fld BETWEEN @.StartDate AND @.EndDate)
> AS tbl
>GROUP BY appeal_codes, appeal_code, appeal_type, leads
>Thanks for the help|||Thanks..
Roy Harvey wrote:
> The assignment of colum aliases:
> AS 'total_cc'
> uses single quotes. Why use quotes? total_cc is a perfectly valid
> column name, and anyway if a column name is in quotes it should be
> "double quotes".
> Looking at the construct:
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN COUNT(o.if_yes_pledge_or_credit_card)
> END AS 'total_cc',
> I am not sure that will work. The more common way to code this -
> assuming I understand what it is supposed to return - is:
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN 1 ELSE 0
> END) AS 'total_cc',
> Looking at the test:
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> I forsee problems with the LIKE. LIKE is for strings, not numbers.
> The HAVING test in the inner query looks like it should be a WHERE
> test.
> I suspect that the use of a derived table is avoidable. Take the
> expression from the inner query that is SUMed in the outer:
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN o.pledge_amount
> END AS 'total_cc_amount',
> Consider
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN o.pledge_amount
> ELSE 0
> END AS total_cc_amount,
> I reworked the whole thing into a single query that I THINK would do
> what you are trying to do. I did not try to fix the LIKE tests and
> such. It will be easier to read using a fixed pitch font.
> SELECT o.appeal_codes,
> o.appeal_type,
> o.appeal_code,
> SUM(CASE WHEN o.call_resolution LIKE 1
> THEN COUNT (o.call_resolution)
> ELSE 0
> END) AS total_yes,
> SUM(o.Pledge_Amount) AS total_yes_amount,
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN 1
> ELSE 0
> END) as total_cc,
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN o.pledge_amount
> ELSE 0
> END) AS total_cc_amount,
> SUM(o.last_gift) AS last_gift_total,
> SUM(CASE WHEN o.call_resolution LIKE 3
> THEN 1
> ELSE 0
> END) AS total_wc,
> SUM(CASE WHEN o.call_resolution LIKE 2
> THEN 1
> ELSE 0
> END) AS total_no,
> COUNT(o.call_resolution) AS total_resolves,
> C.leads
> FROM tlead_sheets AS o
> JOIN tappeal_codes AS c
> ON o.appeal_codes = c.appeal_code
> WHERE o.date_fld BETWEEN @.StartDate AND @.EndDate
> GROUP BY o.appeal_codes, o.appeal_code, o.appeal_type, c.leads
> Hope that helps some.
> Roy Harvey
> Beacon Falls, CT
>
> On 23 May 2006 13:47:49 -0700, vncntj@.hotmail.com wrote:
>