Stored Procedure - Alternate Required

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure - Alternate Required

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-19 : 05:04:47

Hi to All,

this is my stored procedure

CREATE PROCEDURE pr_FetchEmailCampDetailsForSponsor --1,2726,'a',NULL
(
@iSearchType INT = null, /*Search All OR Campaign OR Date*/
@iSponsorID INT = null, /*ProfileID Of Sponsor */
@vCampaignName VARCHAR(100) = null, /*Campaign Name*/
@dCreatedDate DATETIME = null /*Created Date*/
)
AS

/*
Functionality: Fetch Campaign Detail value by sponsorID
Input:
iSearchType : 0- All Details, 1- SearchByName, 2- SearchByDate
isponsorID : Profile ID of Promoter
vCampaignName : Search Campiagn Name
dCreatedDate : Search date
*/
SET XACT_ABORT ON
SET NOCOUNT ON

BEGIN

IF(@iSearchType = 0) -- Search by All Deatils
BEGIN
SELECT ec.vCampaignName, ec.iEmailCampID , 'dCreatedDate'=CONVERT(VARCHAR(40),ec.dCreatedDate,100), ec.vStatus,
'Recipients'= case when ec.iTotalMembers = 0 then '-'
else convert(varchar(50),ec.iTotalMembers) end ,
'EmailViewed'= case when ec.iEmailViewed = 0 then ('-')
else convert(varchar(50),ec.iEmailViewed) end ,
'EmailBounced'= case when ec.iEmailBounced = 0 then ('-')
else convert(varchar(50),ec.iEmailBounced) end ,
'ScheduleAlert'=case when ec.iScheduleAlert = 0 then ('-')
else convert(varchar(50),ec.iScheduleAlert) end ,
'OLRAlert'= case when ec.iOLRAlert = 0 then ('-')
else convert(varchar(50),ec.iOLRAlert) end ,
ea.bFileAttachment, ea.vFilePath,ea.vFileName, 'dScheduledDate'=CONVERT(VARCHAR(40),ed.dScheduledDate,100) , ed.iRecipientListID
FROM
EmailCampaignAttachmentForSponsor ea INNER JOIN EmailCampaignDetailsForSponsor ec ON ea.iEmailCampID = ec.iEmailCampID
LEFT OUTER JOIN EmailCampaignDraftForSponsor ed ON ec.iEmailCampID = ed.iEmailCampID
WHERE
ec.iSponsorID IN (SELECT iProfileID FROM dbo.fn_FetchCompanyIDs(@iSponsorID))
AND ec.bActive = 1 AND ea.bActive = 1 AND ed.bActive = 1
--CONDITION CRITERIAS BELOW
AND ec.vCampaignName LIKE CASE WHEN @vCampaignName IS NULL THEN ec.vCampaignName ELSE '%' + @vCampaignName + '%' END
AND CONVERT(VARCHAR(30),ec.dCreatedDate,101) = CASE WHEN @dCreatedDate IS NULL THEN CONVERT(VARCHAR(30),ec.dCreatedDate,101) ELSE CONVERT(VARCHAR(30),@dCreatedDate,101) END

END

IF(@iSearchType = 1) -- Search by Campaign Name
BEGIN
SELECT ec.vCampaignName, ec.iEmailCampID , 'dCreatedDate'=CONVERT(VARCHAR(40),ec.dCreatedDate,100), ec.vStatus,
'Recipients'= case when ec.iTotalMembers = 0 then '-'
else convert(varchar(50),ec.iTotalMembers) end ,
'EmailViewed'= case when ec.iEmailViewed = 0 then ('-')
else convert(varchar(50),ec.iEmailViewed) end ,
'EmailBounced'= case when ec.iEmailBounced = 0 then ('-')
else convert(varchar(50),ec.iEmailBounced) end ,
'ScheduleAlert'=case when ec.iScheduleAlert = 0 then ('-')
else convert(varchar(50),ec.iScheduleAlert) end ,
'OLRAlert'= case when ec.iOLRAlert = 0 then ('-')
else convert(varchar(50),ec.iOLRAlert) end ,
ea.bFileAttachment, ea.vFilePath,ea.vFileName, 'dScheduledDate'=CONVERT(VARCHAR(40),ed.dScheduledDate,100) , ed.iRecipientListID
FROM EmailCampaignAttachmentForSponsor ea , EmailCampaignDetailsForSponsor ec
left outer join EmailCampaignDraftForSponsor ed on ec.iEmailCampID = ed.iEmailCampID
WHERE ea.iEmailCampID = ec.iEmailCampID AND
ec.iSponsorID IN (select iProfileID from dbo.fn_FetchCompanyIDs(@iSponsorID)) AND
ec.vCampaignName LIKE '%' + @vCampaignName + '%' and
ec.bActive = 1 and ea.bActive = 1 and ed.bActive = 1
END

IF(@iSearchType = 2) -- Search by Campaign Date
BEGIN
SELECT ec.vCampaignName, ec.iEmailCampID ,'dCreatedDate'=CONVERT(VARCHAR(40),ec.dCreatedDate,100), ec.vStatus,
'Recipients'= case when ec.iTotalMembers = 0 then '-'
else convert(varchar(50),ec.iTotalMembers) end ,
'EmailViewed'= case when ec.iEmailViewed = 0 then ('-')
else convert(varchar(50),ec.iEmailViewed) end ,
'EmailBounced'= case when ec.iEmailBounced = 0 then ('-')
else convert(varchar(50),ec.iEmailBounced) end ,
'ScheduleAlert'=case when ec.iScheduleAlert = 0 then ('-')
else convert(varchar(50),ec.iScheduleAlert) end ,
'OLRAlert'= case when ec.iOLRAlert = 0 then ('-')
else convert(varchar(50),ec.iOLRAlert) end ,
ea.bFileAttachment, ea.vFilePath,ea.vFileName, 'dScheduledDate'=CONVERT(VARCHAR(40),ed.dScheduledDate,100) , ed.iRecipientListID
FROM EmailCampaignAttachmentForSponsor ea , EmailCampaignDetailsForSponsor ec
left outer join EmailCampaignDraftForSponsor ed on ec.iEmailCampID = ed.iEmailCampID
WHERE ea.iEmailCampID = ec.iEmailCampID AND
ec.iSponsorID IN (select iProfileID from dbo.fn_FetchCompanyIDs(@iSponsorID)) AND
YEAR(ec.dCreatedDate) = YEAR(@dCreatedDate) AND
MONTH(ec.dCreatedDate) = MONTH(@dCreatedDate) AND
DAY(ec.dCreatedDate) = DAY(@dCreatedDate)
and
ec.bActive = 1 and ea.bActive = 1 and ed.bActive = 1

END

END
--

The concept behind this search based on three condition
1)search irrespective of campaign name ,date
2)search based on campaign name
3)search based on date

so i thought of an alternative by browsing sites

here is my new sp

CREATE PROCEDURE pr_FetchEmailCampDetailsForSponsor1 1,2726,NULL,NULL
(
@iSponsorID INT = null, /*ProfileID Of Sponsor */
@vCampaignName VARCHAR(100) = null, /*Campaign Name*/
@dCreatedDate DATETIME = null /*Created Date*/
)
AS
BEGIN
SELECT ec.vCampaignName, ec.iEmailCampID , 'dCreatedDate'=CONVERT(VARCHAR(40),ec.dCreatedDate,100), ec.vStatus,
'Recipients'= case when ec.iTotalMembers = 0 then '-'
else convert(varchar(50),ec.iTotalMembers) end ,
'EmailViewed'= case when ec.iEmailViewed = 0 then ('-')
else convert(varchar(50),ec.iEmailViewed) end ,
'EmailBounced'= case when ec.iEmailBounced = 0 then ('-')
else convert(varchar(50),ec.iEmailBounced) end ,
'ScheduleAlert'=case when ec.iScheduleAlert = 0 then ('-')
else convert(varchar(50),ec.iScheduleAlert) end ,
'OLRAlert'= case when ec.iOLRAlert = 0 then ('-')
else convert(varchar(50),ec.iOLRAlert) end ,
ea.bFileAttachment, ea.vFilePath,ea.vFileName, 'dScheduledDate'=CONVERT(VARCHAR(40),ed.dScheduledDate,100) , ed.iRecipientListID
FROM
EmailCampaignAttachmentForSponsor ea INNER JOIN EmailCampaignDetailsForSponsor ec ON ea.iEmailCampID = ec.iEmailCampID
LEFT OUTER JOIN EmailCampaignDraftForSponsor ed ON ec.iEmailCampID = ed.iEmailCampID
WHERE
ec.iSponsorID IN (SELECT iProfileID FROM dbo.fn_FetchCompanyIDs(@iSponsorID))
AND ec.bActive = 1 AND ea.bActive = 1 AND ed.bActive = 1
--CONDITION CRITERIAS BELOW
AND ec.vCampaignName LIKE CASE WHEN @vCampaignName IS NULL THEN ec.vCampaignName ELSE '%' + @vCampaignName + '%' END
AND CONVERT(VARCHAR(30),ec.dCreatedDate,101) = CASE WHEN @dCreatedDate IS NULL THEN CONVERT(VARCHAR(30),ec.dCreatedDate,101) ELSE CONVERT(VARCHAR(30),@dCreatedDate,101) END
END

is this correct approach, i have avoided the if conditions

and i also need validation of date. i dont want to use convert in my column so it decreases perform. how to check for dates.

Thanks again!

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-19 : 05:50:13

i have to display records for specified date using @date parameter

CONVERT(VARCHAR(30),ec.dCreatedDate,101) = CONVERT(VARCHAR(30),@date,101)

i dont want to use convert functioanlity for column like this CONVERT(VARCHAR(30),ec.dCreatedDate,101) since it slows performance

what s other alternative.
Note: dCreatedDate is datetime

Thanks Again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 05:57:54

quote:
Originally posted by dineshrajan_it

i have to display records for specified date using @date parameter

CONVERT(VARCHAR(30),ec.dCreatedDate,101) = CONVERT(VARCHAR(30),@date,101)

i dont want to use convert functioanlity for column like this CONVERT(VARCHAR(30),ec.dCreatedDate,101) since it slows performance

what s other alternative.
Note: dCreatedDate is datetime

Thanks Again

may be this

DATEADD(dd,DATEDIFF(dd,0,ec.dCreatedDate),0)=DATEADD(dd,DATEDIFF(dd,0,@date),0)

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-19 : 06:04:45

Hi Vishak,

Thanks for ur reply. is there any other alternative so that i can avoid using functions to column and use only for parameters.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-19 : 06:09:38

quote:
Originally posted by dineshrajan_it

Hi Vishak,

Thanks for ur reply. is there any other alternative so that i can avoid using functions to column and use only for parameters.

This may improve performance

ec.dCreatedDate>=DATEADD(dd,DATEDIFF(dd,0,@date),0) and
ec.dCreatedDate<DATEADD(dd,DATEDIFF(dd,0,@date),1)

Madhivanan

Failing to plan is Planning to fail

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-19 : 06:25:57

Thanks Madhivanan ,

it works. one more question does it consume less time compared to using function for columns.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-19 : 07:08:21

If you have an index on dCreatedTime, yes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources