January 3, 2009 at 10:30 pm
Hi
i have cursor which is not giving me the DEsired Result as
Project Name---A
Task A Task Start Task Finish % Comp.
Project Name---B
Task A Task Start Task Finish % Comp.
PLease review my Proc to achieve above Result.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[SEND_MAIL_IN_BUNCH] Script Date: 02/05/2008 13:01:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SEND_MAIL_IN_BUNCH1] AS
DECLARE
@STR_RESUID NVARCHAR(MAX),
@STR_MAIL NVARCHAR(MAX),@STR_PROJ nvarchar(max),
@tableHTML NVARCHAR(MAX),@tableHTML1 NVARCHAR(MAX);
DECLARE C2 CURSOR FOR SELECT DISTINCT A.WRES_UID_MANAGER from [ProjectServer_PUBLISHED].[dbo].MSP_ASSIGNMENTS A,[ProjectServer_PUBLISHED].[dbo].MSP_PROJECTS P WHERE P.PROJ_UID=A.PROJ_UID;
OPEN C2;
FETCH NEXT FROM C2 INTO @STR_RESUID --FETCH THE NEW RESOURCE ID
WHILE (@@FETCH_STATUS=0)
BEGIN
DECLARE C1 CURSOR FOR SELECT DISTINCT PROJ_NAME from [ProjectServer_PUBLISHED].[dbo].MSP_ASSIGNMENTS A,[ProjectServer_PUBLISHED].[dbo].MSP_PROJECTS P WHERE P.PROJ_UID=A.PROJ_UID AND A.WRES_UID_MANAGER=@STR_RESUID;
OPEN C1;
FETCH NEXT FROM C1 INTO @STR_PROJ
WHILE (@@FETCH_STATUS=0)
BEGIN
SELECT @STR_MAIL=WRES_EMAIL FROM [ProjectServer_PUBLISHED].[dbo].[MSP_RESOURCES] WHERE RES_UID=@STR_RESUID AND WRES_EMAIL IS NOT NULL;
SET @tableHTML =
N' ' +
N' '+
N''+@STR_PROJ+''+
N' '+
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
CAST ( ( SELECT td = P.[PROJ_NAME], '' ,
td = [TASK_NAME], '' ,
td = [RES_NAME], '' ,
td = [ASSN_PCT_WORK_COMPLETE], '' ,
td = CONVERT(VARCHAR,[ASSN_START_DATE],6), '' ,
td = CONVERT(VARCHAR,[ASSN_FINISH_DATE],6), ''
FROM [ProjectServer_PUBLISHED].[dbo].[msp_projects] P,
[ProjectServer_PUBLISHED].[dbo].[MSP_ASSIGNMENTS] A ,
[ProjectServer_PUBLISHED].[dbo].[MSP_RESOURCES] R
WHERE R.RES_UID=A.WRES_UID_MANAGER AND P.PROJ_UID=A.PROJ_UID
AND A.WRES_UID_MANAGER=@STR_RESUID AND PROJ_NAME=@STR_PROJ AND RES_TYPE='2' AND ASSN_FINISH_DATE 100
FOR XML PATH( 'tr'), TYPE ) AS NVARCHAR( MAX) ) +
N' ' +
N' ' +
N' ';
END
FETCH NEXT FROM C1 INTO @STR_PROJ
SET @tableHTML1=@tableHTML1+@tableHTML
INSERT INTO DEBUG VALUES(@tableHTML1);
END
FETCH NEXT FROM C2 INTO @STR_RESUID --THIS CURSOR WILL FETCH THE NEXT VALUE OF CURSOR
CLOSE C1
DEALLOCATE C1
CLOSE C2
DEALLOCATE C2
January 4, 2009 at 3:38 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 5:29 am
Assignment Table --ID, Manager_Mail_Add, Project _UID
Porject Table -- ID, Proj_UID,
Desired O/P:
Project Name : A
Task Name Task Start Task Finish % Comp
Project Name : B
Task Name Task Start Task Finish % Comp
Hope this will do.
January 4, 2009 at 6:42 am
parth83.rawal (1/4/2009)
Hope this will do.
Please read the article that I posted about how to post sample data and table structure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2009 at 5:35 am
First of all, there are at least two mistakes: both "fetch next..." are after the loop and I think you have to close and deallocate C1 in the outer loop.
Second, try to rewrite it as set based. Not much help is possible without metadata. if not possible, you can at least combine two cursors into one.
DECLARE C2 CURSOR FOR SELECT DISTINCT A.WRES_UID_MANAGER,PROJ_NAME from [ProjectServer_PUBLISHED].[dbo].MSP_ASSIGNMENTS A,[ProjectServer_PUBLISHED].[dbo].MSP_PROJECTS P WHERE P.PROJ_UID=A.PROJ_UID ;
...
FETCH NEXT FROM C2 INTO @STR_PROJ,@STR_RESUID
....
@STR_MAIL values looks unused and the next block is a simple string concatenation, so a set based solution is relatively simple.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply