Hi all,

My problem may sound very much weird to you but I hope anyone might have come across this.

I am executing a stored procedure inside a stored procedure. The code which I wish to execute is listed below.

CREATE procedure sp_save_placement_skills_new
@ActionId int,
@NewPlacementId int,
@UserId int
with recompile as
set implicit_transactions off
checkpoint

declare @tableSkillList table (skillName varchar(100) )
declare @tempSkill_ID int
declare @strPlacementSkillsUsed varchar(1000)
declare @strPlacementSkillsUsed_clone varchar(1000)
declare @IntPos int
declare @strSkillName varchar(50)

EXECUTE p_GetActionFieldValue @ActionId, @UserId, 'ContractConfigFields','PlacementSkillsUsed',
@strPlacementSkillsUsed OUTPUT

insert into test_skill_values values(@strPlacementSkillsUsed)


set @strPlacementSkillsUsed = @strPlacementSkillsUsed + ','

set @strPlacementSkillsUsed_clone = @strPlacementSkillsUsed
set @IntPos = CHARINDEX(',',@strPlacementSkillsUsed,1)

if replace(@strPlacementSkillsUsed_clone,',','') <> ''
begin
     while @IntPos > 0
            begin
        set @strSkillName = RTRIM(LTRIM(LEFT(@strPlacementSkillsUsed,@IntPos - 1)))
        Insert into @tableSkillList values(@strSkillName)
        Set @strPlacementSkillsUsed = RIGHT(@strPlacementSkillsUsed,LEN(@strPlacementSki llsUsed)-@IntPos)

        set @IntPos = CHARINDEX(',',@strPlacementSkillsUsed,1)

         end
end


DELETE FROM ContractSkill
WHERE ContractId = @NewPlacementid
    AND SkillId IN (SELECT Skillid
              FROM Skill
              WHERE SkillCategoryid IN
                (SELECT SkillCategoryid
                  FROM SkillCategory
                  WHERE Category = 'PlacementSkillsUsed'))

insert into contractskill select @NewPlacementId ,a.skillid,1 from skill a,@tableSkillList b
where upper(ltrim(rtrim(a.skill))) = upper(ltrim(rtrim(b.skillName))) and a.skillCategoryId IN (select SkillCategoryId from SkillCategory where Category = 'PlacementSkillsUsed')

GO

Basically what I am trying to do is that a comma delimited string is
being split and stored into table which has been created using table variable of sql server 2000. Then I put these values into actual table.

Problem Description
Some times whole of the stored procedure runs sucessfully without any problem at all. Sometimes it fails and is not able to put data into
contractskill table.

After using a debugging technique of putting messages while execution is in process in a table I find that
1) sometimes stored procedure execution terminates abruptly in while loop itself and after that no statement gets executed.
2)Sometimes it will split ok but fail during Delete statement.
3)Sometimes it will simply fail in last insert statement.

I have tried everything I can think of. I have tried recompile option,
setting implicit transactions off, using checkpoint, very thoroughly examined sql profiler to look for any error which I can understand for this abruput behaviour.


Reason I have tried implicit transactions off is due to the fact that
just before execution of this stored procedure set implicit transaction is on and after execution is set to off. But I thought that I would put first line in my stored procedure to set implicit transaction off so that I can take care of this statement.

Any help would be most welcome and thanks in advance. I have already spent too much time but still could not reach to any logical conclusion. By the way table test_skill_values is just being used for
storing messages during execution.


Thanks,
Puneet Mittal






Puneet

Does anybody have a solution for this problem

Puneet,

Depending on the version of SQL Server you are using, you have several options. If you are using SQL Server 2000, i would look into using the RAISERROR statement in your stored procedure. Look in the BOL (Books Online) for this topic.

If you are using SQL Server 2005, you can use the new support TRY...CATCH statements to implement error handling.

Either of these will help you find out where the problem lies.

I hope this helps...

Scott