December 1, 2008 at 10:47 am
I had a project thrown in my lap and don't have time to rewrite another developers code, but need to figure out the last few issues.
In the stored procedure below, we are updating a table based upon a user changing an appraiser assigned to an employee. It works fine 90% of the time, but I need to make some modifications based upon an alternative scenario.
Currently, the WHERE clause states:
WHERE h.AppraiserChange = 1 AND h.Skip = 0 And h.TerminatedUser = 0 AND pa.IsCurrent = 1'
I am going to modify it to
WHERE h.AppraiserChange = 1 AND h.AppraiserChangeType = ''R'' AND h.Skip = 0 And h.TerminatedUser = 0 AND pa.IsCurrent = 1'
What is beyond the scope of my current experience, is adding another step to the UPDATE section. Basically, I need to account for a condition and add the following line
in the UPDATE pa SET section of:
pa.AppraiserComment = NULL,
This would match up to a WHERE clause that follows:
WHERE h.AppraiserChange = 1 AND h.AppraiserChangeType = ''M'' AND h.Skip = 0 And h.TerminatedUser = 0 AND pa.IsCurrent = 1'
Thank you in advance for any assistance you can provide.
/**********************************/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[ImportAppraiserChangeUpdateAppraisalByEmployeeID] @TableID char(36), @ClientID varchar(10)
AS
--Procedure updates all appraisals for users who have an appraiser change in the import.
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
DECLARE @sql varchar(4000)
SET @sql =
'UPDATE pa SET
pa.UnMergeDate = GETDATE(),
pa.MergeDate = NULL,
Merged = 0,
AppraiserReadyDate = NULL,
pa.FromDate = ISNULL(CONVERT(smalldatetime, h.[Appraisal Start Date]),pa.FromDate),
pa.ToDate = ISNULL(CONVERT(smalldatetime, h.[Appraisal End Date]),pa.ToDate)
FROM Perf_Appraisal pa
INNER JOIN [Users] u ON pa.UserID = u.UserID AND u.ClientID = ''' + @ClientID + '''
INNER JOIN z_HrnCustomerImport' + @TableID + ' h on u.EmployeeID = h.[Employee ID]
WHERE h.AppraiserChange = 1 AND h.Skip = 0 And h.TerminatedUser = 0 AND pa.IsCurrent = 1'
PRINT @sql
BEGIN TRAN
EXEC(@sql)
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[ImportAppraiserChangeUpdateAppraisalByEmployeeID] @TableID char(36), @ClientID varchar(10)
AS
--Procedure updates all appraisals for users who have an appraiser change in the import.
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
DECLARE @sql varchar(4000)
SET @sql =
'UPDATE pa SET
pa.UnMergeDate = GETDATE(),
pa.MergeDate = NULL,
Merged = 0,
AppraiserReadyDate = NULL,
pa.FromDate = ISNULL(CONVERT(smalldatetime, h.[Appraisal Start Date]),pa.FromDate),
pa.ToDate = ISNULL(CONVERT(smalldatetime, h.[Appraisal End Date]),pa.ToDate)
FROM Perf_Appraisal pa
INNER JOIN [Users] u ON pa.UserID = u.UserID AND u.ClientID = ''' + @ClientID + '''
INNER JOIN z_HrnCustomerImport' + @TableID + ' h on u.EmployeeID = h.[Employee ID]
WHERE h.AppraiserChange = 1 AND h.Skip = 0 And h.TerminatedUser = 0 AND pa.IsCurrent = 1'
PRINT @sql
BEGIN TRAN
EXEC(@sql)
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
December 1, 2008 at 11:14 am
I'm not sure what you mean. Do you mean you need both conditions? Or update if either is there?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 1, 2008 at 11:19 am
Steve,
I probably need to include both conditions because if the AppraiserChangeType = 'M', I need to update one more field than if the AppraiserChangeType = 'R'.
December 1, 2008 at 11:35 am
It's fairly simple logic in the WHERE clause, no different from a select clause.
In fact, I'd recommend testing with the SELECT first, then changing the update.
You can add another AND condition
WHERE h.AppraiserChange = 1
AND h.AppraiserChangeType = ''R''
AND h.AppraiserChangeType = ''M''
AND h.Skip = 0
And h.TerminatedUser = 0
AND pa.IsCurrent = 1'
to the clause, but it doesn't make sense here, at least from your description. Instead I think you mean an OR for those two items only. So
WHERE h.AppraiserChange = 1
AND
( h.AppraiserChangeType = ''R''
OR h.AppraiserChangeType = ''M''
)
AND h.Skip = 0
And h.TerminatedUser = 0
AND pa.IsCurrent = 1'
Meaning all other conditions and either an "r" or "m" in that field. What you could mean, however was if there is an "r", then we check for an "m" in a related row and update something, which is more complicated.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 1, 2008 at 12:29 pm
I must not be explaining myself very well.
The problem is, I need to have the SET clause look like the following WHERE h.AppraiserChangeType = ''R''
AND h.AppraiserChangeType = ''M''
'UPDATE pa SET
pa.UnMergeDate = GETDATE(),
pa.MergeDate = NULL,
Merged = 0,
AppraiserReadyDate = NULL,
and I need it to look like the following WHERE h.AppraiserChangeType = ''M''
'UPDATE pa SET
pa.UnMergeDate = GETDATE(),
pa.MergeDate = NULL,
pa.AppraiserComment = NULL,
Merged = 0,
AppraiserReadyDate = NULL,
December 1, 2008 at 1:32 pm
How about something like:
UPDATE pa SET
pa.UnMergeDate = GETDATE(),
pa.MergeDate = NULL,
pa.AppraiserComment = CASE WHEN h.AppraiserChangeType = 'M' THEN NULL END,
Merged = 0,
AppraiserReadyDate = NULL
WHERE h.AppraiserChange = 1 AND h.Skip = 0 And h.TerminatedUser = 0 AND pa.IsCurrent = 1
OR
UPDATE pa SET
pa.UnMergeDate = GETDATE(),
pa.MergeDate = NULL,
pa.AppraiserComment = CASE WHEN h.AppraiserChangeType = 'M' THEN NULL ELSE pa.AppraiserComment END,
Merged = 0,
AppraiserReadyDate = NULL
WHERE h.AppraiserChange = 1 AND h.Skip = 0 And h.TerminatedUser = 0 AND pa.IsCurrent = 1
[EDIT] I didn't throw in all your FROM's in these examples. The point being to leave the WHERE clause as it was, and just use a case to update the AppraiserComment field.
December 1, 2008 at 1:55 pm
That should work. Thank you for your assistance!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply