Set GETDATE() when an UPDATE WHERE is dynamic

Question

I have a table named Stored_Procedure_Logging where I store the logging of my stored procedure.

SPD_name       Start_process             End_process
SPD_MySpd      2015-04-09 18:06:50       NULL

The first script will insert the Start time by this way :

INSERT INTO Stored_Procedure_Logging (Start_process,SPD_name) 
VALUES (GETDATE(),OBJECT_NAME(@@PROCID))

And when the SDP finish its run I must update the previous inserted record with the End time with this below script but it does not working !!

I find a NULL value in the second column (End_process)

UPDATE  Stored_Procedure_Logging 
SET End_process = GETDATE() 
WHERE SPD_name =OBJECT_NAME(@@PROCID)

OBJECT_NAME(@@PROCID) is to get the current SPD

Can you help me please ??

Are you sure `OBJECT_NAME(@@PROCID)` is returning the correct value when you run the second query? — Barmar, Apr 09 '15 at 16:24
I'm using SQL Server 2008 R2, in fact the issue is with the second query .. But It works when I try with this (without OBJECT_NAME(@@PROCID) ) : UPDATE Stored_Procedure_Logging SET End_process = GETDATE() WHERE SPD_name ='SPD_MySpd' — Skandar, Apr 09 '15 at 16:42
Can you provide a more real-world example? Meaning are you doing: INSERT _realwork_ UPDATE all in the same sproc? — dmeglio, Apr 09 '15 at 21:39
Yes it's the case my spd is coded as below : CREATE PROCEDURE SPD_MySpd BEGIN INSERT INTO Stored_Procedure_Logging (Start_process,SPD_name) VALUES (GETDATE(),OBJECT_NAME(@@PROCID)) . . . UPDATE Stored_Procedure_Logging SET End_process = GETDATE() WHERE SPD_name =OBJECT_NAME(@@PROCID) END — Skandar, Apr 10 '15 at 10:44

score 0 · Answer 1 · answered Apr 09 '15 at 16:26

0

How ever there is a wrong in update query's where clause. it is may not syntax error but may be OBJECT_NAME(@@PROCID) value is null.

check OBJECT_NAME(@@PROCID) value. may be after processing OBJECT_NAME(@@PROCID) return different value

answered Apr 09 '15 at 16:26

janaka aravinda

I'm using SQL Server 2008 R2, in fact the issue is with the second query .. But It works when I try with this (without OBJECT_NAME(@@PROCID) ) : UPDATE Stored_Procedure_Logging SET End_process = GETDATE() WHERE SPD_name ='SPD_MySpd' – Skandar Apr 09 '15 at 16:43
That makes it pretty clear that the problem is that `OBJECT_NAME(@@PROCID)` is not returning the name you want. What does `SELECT OBJECT_NAME(@@PROCID) FROM dual` return? – Barmar Apr 09 '15 at 16:44
in sql server there is no table call dual (Like oracle). I m not familier with object_name. but you can write like this WHERE SPD_name IN (SELECT OBJECT_NAME(@@PROCID)) – janaka aravinda Apr 09 '15 at 16:48
When i write SELECT OBJECT_NAME(@@PROCID) I get the name my sproc.. OBJECT_NAME(@@PROCID) returns 'nvarchar' but SPD_name is a 'varchar' , is that may be the problem ? – Skandar Apr 10 '15 at 08:53

1 Answers1