need help in my query and data return it
-
I need help,me using SQL Server 2000, here is my SP, its working fine:
CREATE PROCEDURE [AddProject]
(
@PC1MOUID int,
@DamageTypeID int,
@ProjectTypeID int,
@PledgedByDonorID int,
@ProjectName varchar(255),
@ProjectCode char(20),
@PopulationBenefited float,
@Latitude float,
@Longitude float,
@InitialCost money,
@RevisedCost money,
@PartWorkPlan char(9),
@IsPrefabricated tinyint,
@CoveredArea int,
@TotalLength float,
@DamageLength float,
@EngineerEstimatedCost money,
@ContractCost money,
@ReconstructionCategoryID int,
@BridgeCategoryID int,
@RoadProjectID int,
@Remarks varchar(255)
)
AS
BEGIN
SET NOCOUNT OFF
DECLARE @Err int
-------------------------------------------- Set the Project Code value --------------------------------------
--declare variables
declare @temp char(20)
declare @newcode char(20)
declare @lastCode varchar(4)
declare @locationcode char(9)
begin
SET @temp = @ProjectCode + '%'
select @lastCode=substring(ProjectCode, 10, 13) from Projects where ProjectCode = (select MAX(ProjectCode) from Projects where ProjectCode like @temp)
order by ProjectCode DESC
declare @code int
set @code = cast(@lastCode as int)
if(@code <> null OR @code > 0)
begin
set @code = @code + 1
declare @string char(4)
--set @string = left( replicate( '0', 4 ), 4 - len( @code ) ) + cast( @code as char(4) )
set @locationcode = substring(@ProjectCode, 1, 9)
set @newcode = @locationcode + left( replicate( '0', 4 ), 4 - len( @code ) ) + cast( @code as char(4) )
end
else
begin
set @locationcode = substring(@ProjectCode, 1, 9)
set @newcode = @locationcode + '0001'
end
end
-------------------------------------------- /Set the Project Code value --------------------------------------
INSERT
INTO [dbo].[Projects]
(
[PC1MOUID],
[DamageTypeID],
[ProjectTypeID],
[PledgedByDonorID],
[ProjectName],
[ProjectCode],
[PopulationBenefited],
[Latitude],
[Longitude],
[InitialCost],
[RevisedCost],
[PartWorkPlan],
[IsPrefabricated],
[CoveredArea],
[TotalLength],
[DamageLength],
[EngineerEstimatedCost],
[ContractCost],
[ReconstructionCategoryID],
[BridgeCategoryID],
[RoadProjectID],
[Remarks]
 
VALUES
(
@PC1MOUID,
@DamageTypeID,
@ProjectTypeID,
@PledgedByDonorID,
@ProjectName,
@newcode,
@PopulationBenefited,
@Latitude,
@Longitude,
@InitialCost,
@RevisedCost,
@PartWorkPlan,
@IsPrefabricated,
@CoveredArea,
@TotalLength,
@DamageLength,
@EngineerEstimatedCost,
@ContractCost,
@ReconstructionCategoryID,
@BridgeCategoryID,
@RoadProjectID,
@Remarks
 
SET @Err = @@Error
DECLARE @ProjectID int
SELECT @ProjectID = SCOPE_IDENTITY()
--insert intitial activities for the project
if(@ProjectID <> null)
begin
INSERT INTO ActivityProgress VALUES (@ProjectID, 10, getdate(), null, 1)
INSERT INTO ActivityProgress VALUES (@ProjectID, 20, getdate(), null, 1)
INSERT INTO ActivityProgress VALUES (@ProjectID, 30, getdate(), null, 1)
end
RETURN @Err
END
GO
but that data given me for Projects table, in the ProjectCode i have 10 values for each project,
now me have to change the Project Code not change as just padd the values in the Code (that my SP do)to make ProjectCode=13 digits so as run the SP alone and give ProjectCode
as a parameter for each values as i have 6905 codes so i used a loop and make a query but when i run these queries (taken from my SP + add code for loop) its done nothing but
when i seperately run these few queries:
declare @ProjectID int
set @ProjectID=20695
while (@projectID<=27600)
BEGIN ---While Begin
declare @ProjectCode char(20)
select @ProjectCode=ProjectCode from Projects
where rojectID=@ProjectID">ProjectID=@ProjectID
declare @temp char(20)
declare @newcode char(20)
declare @lastCode varchar(4)
declare @locationcode char(9)
begin
SET @temp = @ProjectCode + '%'
select @lastCode=substring(ProjectCode, 10, 13) from Projects where ProjectCode = (select MAX(ProjectCode) from Projects where ProjectCode like @temp)
order by ProjectCode DESC
declare @code int
set @code = cast(@lastCode as int)
if(@code <> null OR @code > 0)
begin
set @code = @code + 1
declare @string char(4)
--set @string = left( replicate( '0', 4 ), 4 - len( @code ) ) + cast( @code as char(4) )
set @locationcode = substring(@ProjectCode, 1, 9)
set @newcode = @locationcode + left( replicate( '0', 4 ), 4 - len( @code ) ) + cast( @code as char(4) )
end
else
begin
set @locationcode = substring(@ProjectCode, 1, 9)
set @newcode = @locationcode + '0001'
end
end
set @ProjectID=@ProjectID+1
END --While End
But it takes too much time and still in progress so i stop it.Plz help me what can i do now as manually checking each query and run it takes too much times in hours and error chances also comes so ple help me , i m in truoble plz help me and give me solution or any idea for solving my problem.
-
déjà vu I gave details of problem like this in another post
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=360507#bm360559
If this is a one off then you would be better off with a set based solution
CREATE TABLE #temp (ProjectCode char(13))
INSERT INTO #temp (ProjectCode char(13))
SELECT MAX(ProjectCode)
FROM [dbo].[Projects]
WHERE ProjectID BETWEEN 20695 AND 27600
GROUP BY ProjectID
UPDATE #temp
SET ProjectCode = LEFT(ProjectCode,1,9) + RIGHT('0000'+CAST(CAST(SUBSTRING(ProjectCode,10,4) as int)+1 as varchar(5)),4)
INSERT
INTO [dbo].[Projects]
([PC1MOUID],
[DamageTypeID],
[ProjectTypeID],
[PledgedByDonorID],
[ProjectName],
[ProjectCode],
...)
SELECT @PC1MOUID,
@DamageTypeID,
@ProjectTypeID,
@PledgedByDonorID,
@ProjectName,
ProjectCode,
...
FROM #temp
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply