I realise some of you guys are well averse to Dynamic SQL and in this instance I wholly agree however the legacy of the design of this application dictates its use. The SQL procedure below is inherited and not my design. I need to speed it up from its current execution time of 25 seconds as it's err supposed to be a real time display. By the way, Query Execution plan denotes 78% table scan in proc bit.

I'm in the middle of re-structuring it myself but fear all I'm doing is tidying. Any suggestions, given that I'm unable to change the table architecture would be gratefully received.

Cheers.

Proc:
Code:
CREATE PROCEDURE sd_rtd_cn  AS DECLARE @Trans VARCHAR(20) DECLARE @CampaignName VARCHAR(40) DECLARE @active BIT DECLARE @sqlquery VARCHAR(4000)
SELECT @Trans = 'ArchiveTransaction'
BEGIN TRANSACTION @Trans
DELETE FROM sd_rtd_campaigns
DECLARE campaignName_Cursor CURSOR FOR SELECT campaignName, active FROM campaigns
OPEN campaignName_Cursor
FETCH NEXT FROM campaignName_Cursor INTO  @CampaignName, @active
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sqlquery = ' DECLARE @fromRetry decimal(9,4) DECLARE @toRetry decimal(9,4) DECLARE @nowRetry decimal(9,4)' +
	' DECLARE @a INT DECLARE @b INT DECLARE @c INT DECLARE @d INT' + 
	' DECLARE @e INT DECLARE @f INT DECLARE @g INT DECLARE @h INT' + 
	' DECLARE @i INT DECLARE @j INT DECLARE @k INT' + 
	' DECLARE @DPNOW varchar(10) DECLARE @DPWRITE varchar(10) DECLARE @cToday INT' +
	' SELECT @DPNOW = cast(datepart(yyyy,getdate()) as varchar) + cast(datepart(mm,getdate()) as varchar) + cast(datepart(dd,getdate()) as varchar)' +
	' SELECT @k = count(recID) FROM [' + @CampaignName + '] WHERE retryTime = 70000.3 AND status=0' +
	' SELECT @a = COUNT(recID) FROM [' + @CampaignName + ']  WHERE status=0' +
	' SELECT @b = count(recID) FROM [' + @CampaignName + '] WHERE status=1' +
	' SELECT @c = count(recID) FROM [' + @CampaignName + '] WHERE status=2' +
	' SELECT @cToday = count(recID) FROM [' + @CampaignName + '] WHERE status=2  AND  cast(datepart(yyyy,WriteTime) as varchar) + cast(datepart(mm,WriteTime) as varchar) + cast(datepart(dd,WriteTime) as varchar) = @DPNOW ' +
	' SET @nowRetry = CONVERT( decimal(9,4),getDate(),2) + 2' +
	' SET @toRetry = @nowRetry + (0.5/24.0) SET @fromRetry = @nowRetry ' +
	' SELECT @d = count(recID) FROM [' + @CampaignName + '] WHERE (retryTime > @fromRetry) AND  (retryTime <= @toRetry) AND (ISNULL(agentID,'''')='''') AND status=0' +
	' SET @toRetry = @nowRetry + (1.0/24.0) SET @fromRetry = @nowRetry + (0.5/24.0) ' +
	' SELECT @e = count(recID) FROM [' + @CampaignName + '] WHERE (retryTime > @fromRetry) AND  (retryTime <= @toRetry) AND (ISNULL(agentID,'''')='''') AND status=0' +
	' SET @toRetry = @nowRetry + (2.0/24.0) SET @fromRetry = @nowRetry + (1.0/24.0) ' +
	' SELECT @f = count(recID) FROM [' + @CampaignName + '] WHERE (retryTime > @fromRetry) AND  (retryTime <= @toRetry) AND (ISNULL(agentID,'''')='''') AND status=0' +
	' SET @toRetry = @nowRetry + (3.0/24.0) SET @fromRetry = @nowRetry + (2.0/24.0) ' +
	' SELECT @g = count(recID) FROM [' + @CampaignName + '] WHERE (retryTime > @fromRetry) AND  (retryTime <= @toRetry) AND (ISNULL(agentID,'''')='''') AND status=0' +
	' SET @toRetry = @nowRetry + (4.0/24.0) SET @fromRetry = @nowRetry + (3.0/24.0) ' +
	' SELECT @h = count(recID) FROM [' + @CampaignName + '] WHERE (retryTime > @fromRetry) AND  (retryTime <= @toRetry) AND (ISNULL(agentID,'''')='''') AND status=0' +
	' SET @fromRetry = @nowRetry + (4.0/24.0) ' +
	' SELECT @i = count(recID) FROM [' + @CampaignName + '] WHERE  (retryTime > @fromRetry) AND (retryTime < 70000.3) AND (ISNULL(agentID,'''')='''') AND status=0' +
	' SET @toRetry = @nowRetry' +
	' SELECT @j = count(recID) FROM [' + @CampaignName + '] WHERE retryTime <= @toRetry AND (ISNULL(agentID,'''')='''') AND status=0' +
	' INSERT INTO sd_rtd_campaigns VALUES(''' + @CampaignName + ''',@c,@cToday,@b,@a,@k,@j,@d,@e,@f,@g,@h,@i,' + CAST(@active AS VARCHAR(16)) + ')'
	

Exec (@sqlQuery)
    FETCH NEXT FROM campaignName_Cursor INTO @CampaignName, @active
END
CLOSE  campaignName_Cursor
COMMIT TRANSACTION ArchiveTransaction
DEALLOCATE campaignName_Cursor
GO