Stored procedure with memory

Sometimes its helpful if a stored procedure can remember the last time it was run.
This is useful for processing data that is time stamped and has been added to a table since the last time the proc was run.

Most of the time keeping track of last runs is done by using a table. This proc demonstrates keeping information in the extended properties of the stored procedure.
Orginal source code is at: http://groups.google.com/group/theseldonvault

Dave

--


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.usp_RememberWhen') IS NOT NULL
DROP PROCEDURE dbo.usp_RememberWhen
go
-- ==========================================================================================
-- Author: Dave Winters
-- Create date: November 2009
-- URL: http://theseldonvault.blogspot.com/
-- Description: Demonstrate stored procedure keeping
-- the time it was last run without using a user table.
-- This technique could also be used to keep track of past parameters.
-- Performance of this technique has not been checked.
-- ==========================================================================================
CREATE PROCEDURE dbo.usp_RememberWhen
AS
BEGIN

SET NOCOUNT ON;
DECLARE @tdate DATETIME
DECLARE @lastdate DATETIME

SET @tdate = GETDATE()

IF NOT EXISTS(SELECT * FROM ::fn_listextendedproperty('last_run', 'schema', 'dbo','Procedure', 'usp_RememberWhen', NULL, NULL) )
EXEC sys.sp_addextendedproperty @name=N'last_run', @value=@tdate ,
@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'PROCEDURE',@level1name=N'usp_RememberWhen'
ELSE
BEGIN
SELECT @lastdate=CONVERT(DATETIME,[value]) FROM ::fn_listextendedproperty('last_run', 'schema', 'dbo','Procedure', 'usp_RememberWhen', NULL, NULL)
EXEC sys.sp_updateextendedproperty @name=N'last_run', @value=@tdate,
@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'PROCEDURE',@level1name=N'usp_RememberWhen'
END
--
-- Print out the value from last run
SELECT @lastdate AS "I RAN WHEN"
END
GO

--
-- Execise the solution
--
SELECT * FROM fn_listextendedproperty('last_run', 'schema', 'dbo', 'PROCEDURE', 'usp_RememberWhen', NULL, NULL)
EXEC usp_RememberWhen
SELECT * FROM fn_listextendedproperty('last_run', 'schema', 'dbo', 'PROCEDURE', 'usp_RememberWhen', NULL, NULL)



--