GetDate() in Stored Procedure

[flanakin]

I have a stored procedure that updates a UpdatedByUserID and UpdateDate columns in a table. For some reason, the user ID is updated, but not the update date. The following SQL is run as DBO:

[blue]UPDATE[/blue] UserDefinedRows
[blue]SET[/blue]    UpdatedByUserId = [green]@UserID[/green],
       UpdateDate = [blue]GETDATE()[/blue]
[blue]WHERE[/blue]  UserDefinedRowId = [green]@UserDefinedRowId[/green]

What's even worse is, if I execute the stored procedure from Query Analyzer, it works fine. Has anyone seen this before? I'm stumped.

________________________________________
Michael Flanakin

http://weblogs.getindigo.net/flanakin

 

[jbenson001]

It should work no matter how you call the procedure. I have never come across this problem. You can eliminate the updating of the UpdateDate in the SP and use a trigger to update it.

Jim

 

[flanakin]

This isn't the only thing I do in that SP, so that would just be more work; but I know that's possible.

________________________________________
Michael Flanakin

http://weblogs.getindigo.net/flanakin

 

[SQLBill]

While that SHOULD work, try this and see if there's a difference:

UPDATE UserDefinedRows
SET    UpdatedByUserId = @UserID
SET    UpdateDate = GETDATE()
WHERE  UserDefinedRowId = @UserDefinedRowId

I removed the comma and added a second SET

-SQLBill


Posting advice: FAQ481-4875

 

[SQLBill]

This is something else you could try:

UPDATE UserDefinedRows
SET    UpdatedByUserId = @UserID,
       UpdateDate = (SELECT GETDATE())
WHERE  UserDefinedRowId = @UserDefinedRowId

-SQLBill

Posting advice: FAQ481-4875