Stored Procedure - SQL Server Forums

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

Other SQL Server Topics (2005)

Stored Procedure

Author

Topic

Nithya
Starting Member

6 Posts

Posted - 2010-05-15 : 12:51:36

ALTER PROCEDURE [dbo].[LCM_CAMPAIGNINFO]

(

@CAMPAIGNID VARCHAR(64),

@LISTCOUNT INTEGER OUTPUT,

@OPENCOUNT INTEGER OUTPUT,

@PCBCOUNT INTEGER OUTPUT,

@LASTSTART DATETIME OUTPUT,

@CONTACTSCOUNT INTEGER OUTPUT,

@CLOSEDCOUNT INTEGER OUTPUT,

@LASTEND DATETIME OUTPUT,

@NEVERDIALED INTEGER OUTPUT,

@DIALOUTS INTEGER OUTPUT,

@SUCCESSCOUNT INTEGER OUTPUT,

@CBCOUNT INTEGER OUTPUT,

@SUCCESSBO INTEGER OUTPUT,

@FAILEDBO INTEGER OUTPUT,

@VOICEOUTCOMECOUNT INTEGER OUTPUT,

@SUCCESSVOICEOUTCOMECOUNT INTEGER OUTPUT,

@FAILEDVOICEOUTCOMECOUNT INTEGER OUTPUT

)

AS

BEGIN

SELECT @LISTCOUNT = COUNT(DISTINCT(LISTID)) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @OPENCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 0

SELECT @PCBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NOT NULL

SELECT @LASTSTART = MAX(STARTTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @CONTACTSCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @CLOSEDCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 1

SELECT @LASTEND = MAX(ENDTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @NEVERDIALED = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND LASTOUTCOME IS NULL

SELECT @DIALOUTS = SUM(MAXRETRIES) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @SUCCESSCOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @CBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NULL

SELECT @SUCCESSBO = COUNT(OBD_AGENTOUTCOME.ContactID) FROM OBD_AGENTOUTCOME JOIN CONTACT ON CONTACT.CONTACTID = OBD_AGENTOUTCOME.ContactID AND CONTACT.STATUS = 1 JOIN OBD_OUTCOME ON OBD_OUTCOME.OUTCOMEID = OBD_AGENTOUTCOME.Outcome AND OBD_OUTCOME.BUSINESSOUTCOME = 1 WHERE OBD_AGENTOUTCOME.CAMPAIGNID = @CAMPAIGNID

SELECT @FAILEDBO = COUNT(ContactID) FROM OBD_AGENTOUTCOME JOIN OBD_OUTCOME ON OBD_OUTCOME.OUTCOMEID = OBD_AGENTOUTCOME.Outcome AND OBD_OUTCOME.BUSINESSOUTCOME = 0 AND OBD_AGENTOUTCOME.CAMPAIGNID = @CAMPAIGNID

SELECT @VOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (00,01,02,03,04,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25)

SELECT @SUCCESSVOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (00,04)

SELECT @FAILEDVOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (01,02,03,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25)

END

Hi friends, am completely new to sp..I just know the basic syntax of sp.. i need the above sp in a more efficient manner. Several output variables are passed but with several "select" queries. i want "case-end" to be implemented to achieve the above sp by grouping the similar conditioned queries into a single statement. can anyone please help me out??? any help would be appreciated. Thanks in advance and expecting ur reply!

Thanks,
Nithya S

Nithya S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-16 : 07:58:25

[code]
...
SELECT @LISTCOUNT = COUNT(DISTINCT(LISTID)) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @OPENCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 0

SELECT @PCBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NOT NULL

SELECT @LASTSTART = MAX(STARTTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @CONTACTSCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @CLOSEDCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 1

SELECT @LASTEND = MAX(ENDTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @NEVERDIALED = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND LASTOUTCOME IS NULL

SELECT @DIALOUTS = SUM(MAXRETRIES) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @SUCCESSCOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @CBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NULL

can be replaced as

SELECT @LISTCOUNT = COUNT(DISTINCT(LISTID)),
@OPENCOUNT = COUNT(CASE WHEN STATUS = 0 THEN CONTACTID ELSE NULL END),
@PCBCOUNT = COUNT(CASE WHEN CALLTYPE = 1 AND USERID IS NOT NULL THEN CONTACTID ELSE NULL END),
@CONTACTSCOUNT = COUNT(CONTACTID),
@CLOSEDCOUNT = COUNT(CASE WHEN STATUS = 1 THEN CONTACTID ELSE NULL END),
@NEVERDIALED = COUNT(CASE WHEN LASTOUTCOME IS NULL THEN CONTACTID ELSE NULL END),
@DIALOUTS = SUM(MAXRETRIES),
@CBCOUNT = COUNT(CASE WHEN CALLTYPE = 1 AND USERID IS NULL THEN CONTACTID ELSE NULL END)
FROM CONTACT
WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @LASTSTART = MAX(STARTTIME),
@LASTEND = MAX(ENDTIME)
FROM OBD_CAMPAIGNSESSION
WHERE CAMPAIGNID = @CAMPAIGNID

SELECT @SUCCESSCOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-05-16 : 11:12:25

"@OPENCOUNT = COUNT(CASE WHEN STATUS = 0 THEN CONTACTID ELSE NULL END),"

Would:

@OPENCOUNT = SUM(CASE WHEN STATUS = 0 THEN 1 ELSE 0 END),

be better? I think giving a WARNING if it counts NULLs - and that warning can sometimes upset Apps

Nithya
Starting Member

6 Posts

Posted - 2010-05-16 : 12:28:29

Visakh and Kristen...........!! Thanks Thanks Thanks a lot for ur help!!! wow!! u people have solved it so easily and it is really clear for me to understand.. and Kristin u mean to say , instead of count(), it would be better to use sum() so that return of NULL can be avoided.. right???

Thanks once again,
Nithya S

Nithya S

Nithya
Starting Member

6 Posts

Posted - 2010-05-16 : 12:35:58

Any other concept other than "case-end" available to make it some more efficient? Thanks in advance!

Thanks & Regards,
Nithya S

Kristen
Test

22859 Posts

Posted - 2010-05-16 : 13:50:32

CASE / END is not a performance consideration here. The important thing here is using Set Based processes which has allowed about 10 separate SELECT statements in your original code to be combined into one.

Nithya
Starting Member

6 Posts

Posted - 2010-05-16 : 14:40:39

Oh ya..Got it! :) Thanks dude!!

Thanks & Regards,
Nithya S

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-17 : 11:11:42

quote:
Originally posted by Kristen

"@OPENCOUNT = COUNT(CASE WHEN STATUS = 0 THEN CONTACTID ELSE NULL END),"

Would:

@OPENCOUNT = SUM(CASE WHEN STATUS = 0 THEN 1 ELSE 0 END),

be better? I think giving a WARNING if it counts NULLs - and that warning can sometimes upset Apps

Yes. Thats why I always avoid COUNT with NULL

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:24:01

quote:
Originally posted by madhivanan

quote:
Originally posted by Kristen

"@OPENCOUNT = COUNT(CASE WHEN STATUS = 0 THEN CONTACTID ELSE NULL END),"

Would:

@OPENCOUNT = SUM(CASE WHEN STATUS = 0 THEN 1 ELSE 0 END),

be better? I think giving a WARNING if it counts NULLs - and that warning can sometimes upset Apps

Yes. Thats why I always avoid COUNT with NULL

Madhivanan

Failing to plan is Planning to fail

yeah thats a valid point
I've had this problem when we use the result of such queries in apps

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nithya
Starting Member

6 Posts

Posted - 2010-05-18 : 06:29:24

Now I have a new question.. "select LISTID from contact" - this query returns the value of listid which is an integer. it may be a single digit number or a double digit number. but i want to round it to 5 digits (ie) 00001 instead of 1 and 00010 instead of 10. can anyone help me in this regard?

Nithya S

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-18 : 08:27:00

Normally you should post a new topic when you have a completely different problem than the original topic. But the answer to your question could be something like this:

select RIGHT('000000' + CAST(LISTID as varchar(20)), 5) from contact

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Nithya
Starting Member

6 Posts

Posted - 2010-05-19 : 01:43:37

Oops!! Apologies for my mistake!! and thanks a lot for ur answer yar!

Nithya S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources