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

Author  Topic  Mredfer Starting Member 4 Posts Posted - 2009-12-03 : 11:03:15 Appologies if this is in the incorrect forum, but I am having a nightmare with this. I have the following stored procedure that looks at a database and creates various projects and links to client information already stored in another database. The procedure will creat a project in the databse and then link to a project to a client (or client department) if it exists, but will create the client if it does not exist. The problem I am getting is that the creation of the clients does not work unless the project is the first in the list. If it is not the first in the list then the project will get linked to the client of the previous project instead and no new client is created. Many thanks */ DECLARE @Contact_ID_# varchar(10) ,@Contract_Code varchar(10) ,@Instructor varchar(30) ,@Contract_Descr1 varchar(40) ,@Contract_Value varchar(10) ,@Office int ,@Department int ,@Analysis_Code_1 int ,@Analysis_Code_2 int ,@Analysis_Code_3 int ,@Analysis_Code_4 int ,@Analysis_Code_5 int ,@Analysis_Code_6 int ,@Analysis_Code_7 int ,@Analysis_Code_8 int ,@Analysis_Code_9 int ,@Analysis_Code_10 int ,@New_Business_Start_Date datetime ,@Portfolio varchar(1) ,@Registered_By varchar(30) ,@iSTATUS int ,@sTEXT varchar(255) ,@sGUID varchar(255) ,@iMAND_ANAL int ,@iMAND_ANALHIST int ,@iMAND_ANALPROJCLIENT int ,@iProj int ,@iProjc int ,@iProjc1 int ,@sProjc1 varchar(30) ,@iClient int ,@dtToday datetime ,@iClihead int ,@vDESC varchar(90) ,@vPCENTRE_CODE varchar(30) ,@vLEDGER_CODE varchar(30) ,@iACCDB_CURR_ID int ,@iSCHEME_ID int ,@Formal_name varchar(200) ,@vPCENTRE_DESC varchar(40) ,@vDEP_CODE varchar(30) ,@dtMaxDet datetime SET @dtToday = GETDATE() DECLARE csr CURSOR LOCAL FAST_FORWARD FOR SELECT RIGHT('000000' + RTRIM([Contact_ID_#]), 6) ,RIGHT('000000' + CAST([Contract_Code] as varchar(10)),6) ,RTRIM(ISNULL([Instructor],'')) ,RIGHT('000000' + CAST([Contract_Code] as varchar(10)),6) + ' ' + RTRIM([Contract_Descr1]) ,RTRIM(ISNULL([Contract_Value],'0')) ,[Office] ,[Department] ,[CONTRACT].[Analysis_Code_1] ,[CONTRACT].[Analysis_Code_2] ,[CONTRACT].[Analysis_Code_3] ,ISNULL([CONTRACT].[Analysis_Code_4],0) ,[CONTRACT].[Analysis_Code_5] ,[CONTRACT].[Analysis_Code_6] ,[CONTRACT].[Analysis_Code_7] ,[CONTRACT].[Analysis_Code_8] ,[CONTRACT].[Analysis_Code_9] ,[CONTRACT].[Analysis_Code_10] ,[New_Business_Start_Date] ,RTRIM(ISNULL([Portfolio],'N')) ,RTRIM(ISNULL([CONTRACT].[Registered_By],'')) ,PCENTRE_CODE ,PCENTRE_DESC ,ACCDB_CURR_ID ,DEP_CODE FROM [SOTRANSFER].[dbo].[CONTRACT] JOIN PROJECT ON PROJ_ID = [Department] JOIN PROFIT_CENTRE ON PCENTRE_ID = [Office] JOIN ACCOUNTS_DB ON ACCDB_ID = PCENTRE_ACCDB_ID JOIN DEPARTMENT ON DEP_CODE = PCENTRE_CODE WHERE CONTRACT.Import_Flag IS NULL OR CONTRACT.Import_Flag = '0' FOR READ ONLY -- Open the cursor for processing OPEN csr FETCH NEXT FROM csr INTO @Contact_ID_#, @Contract_Code, @Instructor, @Contract_Descr1, @Contract_Value,@Office,@Department,@Analysis_Code_1, @Analysis_Code_2,@Analysis_Code_3,@Analysis_Code_4, @Analysis_Code_5,@Analysis_Code_6,@Analysis_Code_7, @Analysis_Code_8,@Analysis_Code_9,@Analysis_Code_10, @New_Business_Start_Date,@Portfolio,@Registered_By, @vPCENTRE_CODE, @vPCENTRE_DESC, @iACCDB_CURR_ID, @vDEP_CODE -- Process each row in the cursor until the fetch status is -1 (no more unread rows) WHILE @@FETCH_STATUS > -1 BEGIN --Create new project from the appropriate template Execute dbo.SP_DUPLICATE_PROJECT @iPROJ_ID = @Department, @sPROJ_DESC = @Contract_Descr1, @sPROJ_CODE = @Contract_Code, @sPROJ_START_DATE = @New_Business_Start_Date, @iRETURN_STATUS = @iSTATUS, @sRETURN_TEXT = @sTEXT, @sRETURN_GUID = @sGUID, @iRETURN_MAND_ANAL = @iMAND_ANAL, @iRETURN_MAND_ANALHIST = @iMAND_ANALHIST, @iRETURN_MAND_ANALPROJCLIENT = @iMAND_ANALPROJCLIENT IF ISNULL(@iSTATUS,0) = 0 BEGIN SELECT @iProj = MAX(PROJ_ID) FROM PROJECT --Assign project status and analysis values UPDATE PROJECT SET PROJ_ANAL_ID_1 = @Analysis_Code_1, PROJ_ANAL_ID_2 = @Analysis_Code_3, PROJ_ANAL_ID_3 = @Analysis_Code_2, PROJ_ANAL_CODE_1 = vw_ANAL_P1_DESC.ANAL_CODE, PROJ_ANAL_CODE_2 = vw_ANAL_P2_DESC.ANAL_CODE, PROJ_ANAL_CODE_3 = vw_ANAL_P3_DESC.ANAL_CODE, PROJ_STATUS = CASE UPPER(@Analysis_Code_7) WHEN 1 THEN 2 WHEN 0 THEN 4 ELSE 0 END, PROJ_END_DATE = NULL, PROJ_PC_COMPLETE = 0, PROJ_PC_CMP_AS_AT = ISNULL(@New_Business_Start_Date,@dtToday), PROJ_CONTRACT = RTRIM(LTRIM(ISNULL(@Contract_Descr1,'') )) FROM PROJECT LEFT OUTER JOIN vw_ANAL_P1_DESC ON vw_ANAL_P1_DESC.ANAL_ID = CAST(@Analysis_Code_1 as int) LEFT OUTER JOIN vw_ANAL_P2_DESC ON vw_ANAL_P2_DESC.ANAL_ID = CAST(@Analysis_Code_2 as int) LEFT OUTER JOIN vw_ANAL_P3_DESC ON vw_ANAL_P3_DESC.ANAL_ID = CAST(@Analysis_Code_3 as int) WHERE PROJ_ID = @iProj --Create a new project detail record IF NOT EXISTS (SELECT PROJDET_PROJ_ID FROM PROJECTDET WHERE PROJDET_PROJ_ID = @iProj) BEGIN INSERT INTO [dbo].[PROJECTDET] ([PROJDET_PROJ_ID] ,[PROJDET_DATE_FROM] ,[PROJDET_MANAGER_ID] ,[PROJDET_ANAL_CODE_1] ,[PROJDET_ANAL_CODE_2] ,[PROJDET_UPD_DATE] ,[PROJDET_UPD_USER] ,[PROJDET_CREATE_DATE] ,[PROJDET_CREATE_USER] ,[PROJDET_IM_GUID]) SELECT [PROJDET_PROJ_ID] = @iProj ,[PROJDET_DATE_FROM] = PROJ_START_DATE ,[PROJDET_MANAGER_ID] = PROJ_MANAGER ,[PROJDET_ANAL_CODE_1] = CAST(@Contract_Value as varchar(15)) ,[PROJDET_ANAL_CODE_2] = CAST(ISNULL(@Analysis_Code_4,0) as varchar(15)) ,[PROJDET_UPD_DATE] = @dtToday ,[PROJDET_UPD_USER] = PROJ_UPD_USER ,[PROJDET_CREATE_DATE] = @dtToday ,[PROJDET_CREATE_USER] = PROJ_UPD_USER ,[PROJDET_IM_GUID] = NEWID() FROM PROJECT WHERE PROJ_ID = @iProj END ELSE BEGIN --There might be several project history records on --the template that was duplicated, so must find the --most recent. SELECT @dtMaxDet = MAX(PROJDET_DATE_FROM) FROM PROJECTDET WHERE PROJDET_PROJ_ID = @iProj UPDATE PROJECTDET SET PROJDET_ANAL_CODE_1 = CAST(@Contract_Value as varchar(15)), PROJDET_ANAL_CODE_2 = CAST(@Analysis_Code_4 as varchar(15)) WHERE PROJDET_PROJ_ID = @iProj AND PROJDET_DATE_FROM = @dtMaxDet END --Lookup client SELECT @iClient = CLIENT_ID FROM CLIENT WHERE (LEFT(CLIENT_LEDGER_CODE, 6) LIke RTRIM(@Contact_ID_#) OR '0' + LEFT(CLIENT_LEDGER_CODE, 6) LIke RTRIM(@Contact_ID_#)) AND CLIENT_PCENTRE_ID = @Office --If client / profit centre combination does not exist, create it IF @iClient is NULL BEGIN SELECT @iClihead = CLIHEAD_ID, @Formal_name = CLIHEAD_DESC FROM CLIENT_HEADER WHERE CLIHEAD_CODE = @Contact_ID_# IF @iClihead IS NULL BEGIN SELECT @Formal_name = RTRIM(LTRIM(Formal_Name)) FROM SOTRANSFER..CLIENTDB WHERE Contact_ID = CAST(@Contact_ID_# as int) SELECT @iClihead = CLIHEAD_ID FROM CLIENT_HEADER WHERE --CLIHEAD_CODE = @Formal_name CLIHEAD_CODE = @Contact_ID_# SELECT TOP 1 @Contact_ID_# = CAST(Contact_ID as varchar) FROM SOTRANSFER..CLIENTDB WHERE RTRIM(LTRIM(Formal_Name)) = @Formal_name END SELECT @vDESC = @vPCENTRE_DESC, @vLEDGER_CODE = @Contact_ID_# + @vDEP_CODE SELECT @iSCHEME_ID = SCHEME_ID FROM DEPARTMENT JOIN LOCATION ON LOC_ID = DEP_LOC_ID JOIN AREA ON AREA_ID = LOC_AREA_ID JOIN COUNTRY ON COUNTRY_ID = AREA_COUNTRY_ID JOIN SCHEMES ON LEFT(SCHEME_DESC,10) = COUNTRY_CODE WHERE DEP_PCENTRE_ID = @Office SELECT @iClient = MAX(CLIENT_ID) + 1 FROM CLIENT Execute dbo.SharpOwl_SP_MAINTAIN_CLIENT_DIVISION @iMASK = 1, @iCLIENTHEADER_ID = @iClihead, @iDIVISION_ID = @iClient, @sDIV_DESC = @vDESC, @sDIV_CODE = @vPCENTRE_CODE, @sDIV_COMMENTS = Null, @sDIV_LEDGER_CODE = @vLEDGER_CODE, @iDIV_CURR_ID = @iACCDB_CURR_ID, @sDIV_VAT_NO = Null, @iDIV_INV_ID = 3, @iDIV_LAYOUT_ID = @iSCHEME_ID, @cDIV_VAT_DUE = '1', @cDIV_SCH_BY_PROJ = '1', @iDIV_TRIANGULATE = 0, @iDIV_PCENTRE = @Office, @iDIV_APPR_REQ = 0, @iDIV_APPR_TIME = 0, @iDIV_BTC_DISBT_ID = Null, @sDIV_BTC_DISB_DESC = Null, @iDIV_ADD_ID = Null, @sDIV_FORMAL_NAME = @Formal_name, @iDIV_INACTIVE = 0, @dtDIV_INACTIVE_DATE = Null, @sDIV_INACTIVE_USER = Null END --Find next project ownership id SELECT @iProjc = MAX(PROJC_ID) + 1 FROM PROJ_CLIENT IF @iProjc IS NULL SET @iProjc = 1 SELECT @iProjc1 = CLIENT_ID, @sProjc1 = CLIENT_CODE FROM CLIENT WHERE -- LEFT(CLIENT_LEDGER_CODE,6) = @Contact_ID_# -- AND CLIENT_PCENTRE_ID = @Office CLIENT_ID = @iClient -- -- IF @iProjc1 IS NULL -- SELECT TOP 1 -- @iProjc1 = CLIENT_ID, -- @sProjc1 = CLIENT_CODE -- FROM CLIENT -- WHERE -- LEFT(CLIENT_LEDGER_CODE,6) = @Contact_ID_# [green]IF NOT (@iProjc1 IS NULL OR @iClient IS NULL) BEGIN IF NOT EXISTS (SELECT PROJC_ID FROM PROJ_CLIENT WHERE PROJC_PROJ_ID = @iProj AND PROJC_CLIENT_ID = @iClient) --Create a link to the client INSERT INTO [dbo].[PROJ_CLIENT] ([PROJC_PROJ_ID] ,[PROJC_CLIENT_ID] ,[PROJC_PCT_OWNED] ,[PROJC_UPD_DATE] ,[PROJC_UPD_USER] ,[PROJC_PCT_CHARGE] ,[PROJC_ANAL_ID_1] ,[PROJC_ANAL_CODE_1] ,[PROJC_ANAL_CODE_2] ,[PROJC_SUPPRESS_BILL] ,[PROJC_FACTOR] ,[PROJC_FACTOR_OWNED] ,[PROJC_FACTOR_CHARGE] ,[PROJC_ID] ,[PROJC_IM_GUID]) VALUES (@iProj ,@iClient ,100 ,@dtToday ,@Registered_By ,100 ,@iProjc1 ,@sProjc1 ,Null ,0 ,Null ,1000 ,1000 ,@iProjc ,Newid()) END END UPDATE SOTRANSFER..CONTRACT SET Import_Flag = CASE @iSTATUS WHEN 1 THEN 0 ELSE 1 END, Import_Date = CASE @iSTATUS WHEN 1 THEN Null ELSE @dtToday END WHERE Contract_Code = CAST(@Contract_Code as int) FETCH NEXT FROM csr INTO @Contact_ID_#, @Contract_Code, @Instructor, @Contract_Descr1, @Contract_Value,@Office,@Department,@Analysis_Code_1, @Analysis_Code_2,@Analysis_Code_3,@Analysis_Code_4, @Analysis_Code_5,@Analysis_Code_6,@Analysis_Code_7, @Analysis_Code_8,@Analysis_Code_9,@Analysis_Code_10, @New_Business_Start_Date,@Portfolio,@Registered_By, @vPCENTRE_CODE, @vPCENTRE_DESC, @iACCDB_CURR_ID, @vDEP_CODE END CLOSE csr DEALLOCATE csr GO I Always Need HEEEELP!!