SQLTeam.com | Weblogs | Forums

Existing records are being overwritten with a new record. Any ideas?


#1

Greetings again mates,

The following stored proc is used to update an existing record, one record at a time depending on any of the input parameters passed.

This worked until I was asked to also give users the ability to overwrite an existing file upload.

Since I added the filePath and filename to be updated, when I hit the submit records, this new submit over-writes ALL exsiting records.

Can anyone please tell me what I am doing wrong?

I know there is a better way to formulate the IF...ELSE...IF statements.

Thanks a lot in advance

ALTER PROCEDURE [dbo].[SP_UpdGroupEvents]
(
	   @EmpID nvarchar(12)=null OUTPUT 
	  ,@jobTitle nvarchar(300)=null OUTPUT
	  ,@RequestedTitle nvarchar(MAX)=null OUTPUT
	  ,@TitleChangeComments nvarchar(MAX)=null OUTPUT
	  ,@payGrade nvarchar(300)=null OUTPUT
	  ,@RequestedPay nvarchar(MAX)=null OUTPUT
	  ,@PayChangeComments nvarchar(MAX)=null OUTPUT
	  ,@AppealClass nvarchar(300)=null OUTPUT
	  ,@RequestedClass nvarchar(MAX)=null OUTPUT 
	  ,@ClassChangeComments nvarchar(MAX)=null OUTPUT
      ,@EmpSignature nvarchar(125)=null OUTPUT
      ,@finishedOrUnfinished nvarchar(200)=null OUTPUT
	  ,@DeptID nvarchar(50)=null
	  ,@apFileName nvarchar(100)=null        
      ,@apFilepath nvarchar(100)=null
	  ,@DirectorID nvarchar(12)=null
)
AS


BEGIN

IF @jobTitle IS NOT NULL 
  BEGIN
	  UPDATE Mass_Events SET 
       [AppealTitle] = @JobTitle
      ,[RequestedTitle] = @RequestedTitle
	  ,[TitleChangeComments] = @TitleChangeComments
	  ,[AppealPayGrade] = @PayGrade
	  ,[RequestedPayGrade] = @RequestedPay
      ,[PayChangeComments] = @PayChangeComments
	  ,[AppealClass] = @AppealClass
	  ,[RequestedClass] = @RequestedClass
      ,[ClassChangeComments] = @ClassChangeComments
      ,[EmpSignature] = @EmpSignature
      ,[EmpSignDate] = getDate()
      ,[finishedOrUnfinished]= @finishedOrUnfinished
	  ,[Deptnumber] =  @DeptID
	  ,[filenameName] = @DirectorID+''+@apFileName
	  ,[Filepath] = +'~/uploads/'+@DirectorID+''+@apFileName
	   WHERE AppealTitle = @JobTitle
   END
 ELSE IF @PayGrade IS NOT NULL 
   BEGIN
		UPDATE Mass_Events SET 
       [AppealTitle] = @JobTitle
      ,[RequestedTitle] = @RequestedTitle
	  ,[TitleChangeComments] = @TitleChangeComments
	  ,[AppealPayGrade] = @PayGrade
	  ,[RequestedPayGrade] = @RequestedPay
      ,[PayChangeComments] = @PayChangeComments
	  ,[AppealClass] = @AppealClass
	  ,[RequestedClass] = @RequestedClass
      ,[ClassChangeComments] = @ClassChangeComments
      ,[EmpSignature] = @EmpSignature
      ,[EmpSignDate] = getDate()
      ,[finishedOrUnfinished]= @finishedOrUnfinished
	  ,[Deptnumber] =  @DeptID
	  ,[filenameName] = @DirectorID+''+@apFileName
	  ,[Filepath] = +'~/uploads/'+@DirectorID+''+@apFileName
	   WHERE appealPayGrade = @PayGrade
	END
ELSE IF @AppealClass IS NOT NULL 

	BEGIN
		UPDATE Mass_Events SET 
      [AppealTitle] = @JobTitle
      ,[RequestedTitle] = @RequestedTitle
	  ,[TitleChangeComments] = @TitleChangeComments
	  ,[AppealPayGrade] = @PayGrade
	  ,[RequestedPayGrade] = @RequestedPay
      ,[PayChangeComments] = @PayChangeComments
	  ,[AppealClass] = @AppealClass
	  ,[RequestedClass] = @RequestedClass
      ,[ClassChangeComments] = @ClassChangeComments
      ,[EmpSignature] = @EmpSignature
      ,[EmpSignDate] = getDate()
      ,[finishedOrUnfinished]= @finishedOrUnfinished
	  ,[Deptnumber] =  @DeptID
	  ,[filenameName] = @DirectorID+''+@apFileName
	  ,[Filepath] = +'~/uploads/'+@DirectorID+''+@apFileName
	   WHERE AppealClass = @AppealClass
	END
END

#2

Logically I don't see anything wrong in your code. You might want to run an equivalent select query and see what records are affected for each type of parameter. What I mean is the following:

CREATE  PROCEDURE [dbo].[SP_UpdGroupEvents_TEST]
(
	   @EmpID nvarchar(12)=null OUTPUT 
	  ,@jobTitle nvarchar(300)=null OUTPUT
	  ,@RequestedTitle nvarchar(MAX)=null OUTPUT
	  ,@TitleChangeComments nvarchar(MAX)=null OUTPUT
	  ,@payGrade nvarchar(300)=null OUTPUT
	  ,@RequestedPay nvarchar(MAX)=null OUTPUT
	  ,@PayChangeComments nvarchar(MAX)=null OUTPUT
	  ,@AppealClass nvarchar(300)=null OUTPUT
	  ,@RequestedClass nvarchar(MAX)=null OUTPUT 
	  ,@ClassChangeComments nvarchar(MAX)=null OUTPUT
      ,@EmpSignature nvarchar(125)=null OUTPUT
      ,@finishedOrUnfinished nvarchar(200)=null OUTPUT
	  ,@DeptID nvarchar(50)=null
	  ,@apFileName nvarchar(100)=null        
      ,@apFilepath nvarchar(100)=null
	  ,@DirectorID nvarchar(12)=null
)
AS


BEGIN

IF @jobTitle IS NOT NULL 
  BEGIN
	  SELECT * FROM Mass_Events
	  WHERE AppealTitle = @JobTitle
   END
 ELSE IF @PayGrade IS NOT NULL 
   BEGIN
	  SELECT * FROM Mass_Events
	  WHERE appealPayGrade = @PayGrade
	END
ELSE IF @AppealClass IS NOT NULL 

	BEGIN
	  SELECT * FROM Mass_Events
	  WHERE AppealClass = @AppealClass
	END
END