How to get last processed date of stored procedure

Hello everybody
Help me to get the last processed date of a stored procedure.The last time it was invoked, or the last time the code was altered?|||... and to short cut a bit.

Invocation cannot be established after the event - you would need to log the execution somewhere or be running a trace.
Alteration only shows up in SS 2005 - SS 2000 only has a creation date so if you altered rather than drop\ created you are out of luck.|||The last time it was invoked, or the last time the code was altered?

i just want the date on which the stored procedure was executed and where the lastexcuted date and other details are stored?|||i just want the date on which the stored procedure was executed and where the lastexcuted date and other details are stored?

one more doubt plz

1) the date on which the stored procedure was excuted last time
2)i want to know whether the stored procedure was excuted successfully or end with failure|||As Pootle Flump mentioned, you will have to add that audit trail detail to the code of your stored procedure. SQL Server (nor any other RDBMS that I know of) will keep this sort of information.|||I create my own logging for all stroed procedures...comes in very handy

Just call this...get the datetime on the way in, and the last datetime on the way out

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_LogProcCalls]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_LogProcCalls]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[usp_LogProcCalls] (
@.SprocName sysname
, @.TranStart datetime
, @.TranEnd datetime
, @.APPUSER char(30)
, @.Rows int
, @.Err int
, @.Paramters varchar(255)
, @.rc int OUTPUT)
AS
SET NOCOUNT ON
--
-- Enterprise Solutions
--
-- File: \\paerscbvd0014\Succession\Procedures
-- Date: 01/04/2006
-- Author: Brett Kaiser
-- Server: paerscbvd0014
-- Database: Succession
-- Login: sa
-- Description: This Procedure will log all procedures executed in a database
--
--
-- The stream will do the following:
--
-- '1. Function...

--
-- Tables Used: Sproc_Log
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- ------------------------------
-- Sproc_Log 0 0 KB 0 KB 0 KB 0 KB
--
-- sp_spaceused Sproc_Log

--Change Log
--
-- UserId Date Description
-- ---- ----- ---------------------------
-- x002548 01/01/2006 1. Initial release
--
--
--

Declare @.error int, @.RowCount int, @.Error_Message varchar(255), @.Error_Type int, @.Error_Loc int

BEGIN TRAN
DECLARE @.LogStart datetime
SELECT @.rc = 0, @.LogStart = GetDate()
IF (SELECT @.@.TRANCOUNT) <> 1
BEGIN
SELECT @.Error_Loc = 1
, @.Error_Message = 'The logging procedure must be executed outside of any transaction. @.@.TRANSCOUNT='
+ CONVERT(varchar(5),@.@.TRANCOUNT)
, @.Error_Type = 50002, @.rc = -6661
GOTO usp_LogProcCalls_Error
END

INSERT INTO Sproc_Log (
[SprocName]
, [TranStart]
, [TranEnd]
, [APP_USER]
, [LogStart]
, [LogEnd]
, [Rows]
, [Err]
, [Paramters])
SELECT
@.SprocName
, @.TranStart
, @.TranEnd
, @.APPUSER
, @.LogStart
, GetDate()
, @.Rows
, @.Err
, @.Paramters

Select @.RowCount = @.@.ROWCOUNT, @.error = @.@.error

IF @.error <> 0
BEGIN
SELECT @.Error_Loc = 2, @.Error_Type = 50001, @.rc = -6662
GOTO usp_LogProcCalls_Error
END

IF @.RowCount <> 1
BEGIN
SELECT @.Error_Loc = 3
, @.Error_Message = 'Expected 1 row to be inserted in to the sproc log. Actual Number inserted = '
+ CONVERT(varchar(5),@.RowCount)
, @.Error_Type = 50002, @.rc = -6663
GOTO usp_LogProcCalls_Error
END

COMMIT TRAN

usp_LogProcCalls_Exit:

-- Place any house keeping procedures here like...

--Set ansi_warnings ON
SET NOCOUNT OFF

RETURN

usp_LogProcCalls_Error:

Rollback TRAN

If @.Error_Type = 50001
BEGIN

Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
-- + ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error)
END

If @.Error_Type = 50002

BEGIN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END

RAISERROR @.Error_Type @.Error_Message

GOTO usp_LogProcCalls_Exit

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO