Update SP 1

[robertfah]

I've got an Update SP that updates fields in a table. The table has 30+ fields so we came up with a SP that would only update whatever parameters were passed into it:

ALTER  PROCEDURE [dbo].[UpdateIssue]
	@IssueID int, 
	@ReceivedDate datetime = NULL, 
	@DueDate datetime = NULL, 
	@ResolvedDate datetime = NULL, 
	@ResolvedByUserID int = NULL, 
	@AssignedGroupID int = NULL, 
	@AssignedUserID int = NULL
        --The rest have been omitted.
AS

	UPDATE Issues
	SET ReceivedDate = ISNULL(@ReceivedDate, ReceivedDate),
			DueDate = ISNULL(@DueDate, DueDate),
			--ResolvedDate = ISNULL(@ResolvedDate, ResolvedDate),
			ResolvedDate = @ResolvedDate,
			--ResolvedByUserID = ISNULL(@ResolvedByUserID, ResolvedByUserID),
			ResolvedByUserID = @ResolvedByUserID,
			AssignedGroupID = ISNULL(@AssignedGroupID, AssignedGroupID),
			AssignedUserID = ISNULL(@AssignedUserID, AssignedUserID)
	WHERE IssueID = @IssueID

	SELECT @@ROWCOUNT AS RecordsAffected

This works great with 1 exception.....for fields that can go from having a value to being null. In that case, this SP ignores it and the field doesn't get updated. For instance, if a user opens an issue and puts a "Due Date" in of 9/22/2011 and saves it...then later realizes he didn't want to do that and clears the field out. The program should update the table and set the Due Date to NULL but again, it doesn't because the new value is NULL.

One thing to note is that table has a field called LastUpdatedDate and we would like that to reflect when the row was truely "lastUpdated"...so if I make the field not default and then it's always updated to matter what, then the LastUpdated field isn't an accurate record.

Any suggestions on how to make a SP to do what I want to do?

 

[starsky51]

If NULL is a legitimate value, you'll have to use some unique default values to identify non-filled parameters. The LastUpdated calculation would have to check whether any of the parameters have changed from their default values. Here's an example (not tested):

ALTER  PROCEDURE [dbo].[UpdateIssue]
    @IssueID int,
    @DueDate datetime = '9999-12-31 23:59:59', 
    @AssignedUserID int = 9999999
        --The rest have been omitted.
AS

    UPDATE Issues
    SET DueDate = (CASE WHEN @DueDate = '9999-12-31 23:59:59'
					 THEN DueDate ELSE @DueDate END),
        AssignedUserID = (CASE WHEN @AssignedUserID = 9999999
							THEN AssignedUserID ELSE @AssignedUserID END),
        LastUpdated = (CASE WHEN @DueDate = '9999-12-31 23:59:59'
							 AND @AssignedUserID = 9999999
						 THEN LastUpdated ELSE GETDATE() END)
    WHERE IssueID = @IssueID

    SELECT @@ROWCOUNT AS RecordsAffected

 

[robertfah]

If NULL is a legitimate value, you'll have to use some unique default values to identify non-filled parameters. The LastUpdated calculation would have to check whether any of the parameters have changed from their default values. Here's an example (not tested):

ALTER  PROCEDURE [dbo].[UpdateIssue]
    @IssueID int,
    @DueDate datetime = '9999-12-31 23:59:59', 
    @AssignedUserID int = 9999999
        --The rest have been omitted.
AS

    UPDATE Issues
    SET DueDate = (CASE WHEN @DueDate = '9999-12-31 23:59:59'
                     THEN DueDate ELSE @DueDate END),
        AssignedUserID = (CASE WHEN @AssignedUserID = 9999999
                            THEN AssignedUserID ELSE @AssignedUserID END),
        LastUpdated = (CASE WHEN @DueDate = '9999-12-31 23:59:59'
                             AND @AssignedUserID = 9999999
                         THEN LastUpdated ELSE GETDATE() END)
    WHERE IssueID = @IssueID

    SELECT @@ROWCOUNT AS RecordsAffected

Thanks a bunch...this is working great so far. I've got to add my other parameters to it and give it a good QA but I think it'll be ok!

Thanks a bunch!

 

[robertfah]

Quick question.....for fields that are Required and do not allow NULLS, can I do something like this:

@ReceivedDate datetime = NULL

ReceivedDate = (CASE WHEN @ReceivedDate = NULL THEN ReceivedDate ELSE @ReceivedDate END)

right?

 

[gmmastros]

That won't work as written, you need to change the = to IS.

ReceivedDate =    (CASE WHEN @ReceivedDate [!]Is[/!] NULL                    THEN ReceivedDate ELSE @ReceivedDate END)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Thanks GMM....got another quick question....1 of the parameters is a text data type....it's not required and it's value can go from either having something to having nothing (NULL). How can I set a default value for this so I know if it's changed?

I tried have it declared as so:

@IssueResolution text = 'xxxxx'

and it doesn't complain about that..only when I use it in the CASE statement, like so:

IssueResolution =(CASE WHEN @IssueResolution = 'xxxxx'
THEN IssueResolution ELSE @IssueResolution END)

I get the following error:

Msg 402, Level 16, State 1, Procedure UpdateIssue, Line 142
The data types text and varchar are incompatible in the is operator.

I tried:

IssueResolution =(CASE WHEN @IssueResolution = convert(text, 'xxxxx')
THEN IssueResolution ELSE @IssueResolution END)

but got this:

Msg 402, Level 16, State 1, Procedure ASC_SCS_UpdateIssue, Line 142
The data types text and text are incompatible in the is operator.

thoughts?

 

[robertfah]

Disregard, I believe I found the solution:

@IssueResolution text = NULL

....

IssueResolution =(CASE WHEN DATALENGTH(@IssueResolution) = 0
		THEN IssueResolution ELSE @IssueResolution END)

This seems to work...