Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2010
    Posts
    1

    Unanswered: Log Usage of stored proc

    HI Everybody,

    My question is how can we log the usage of a stored proc in a specific table. for example let's take a stored proc

    CREATE PROCEDURE dbo.Hills_GetO_ID
    (@P_ID as Varchar(20))

    As

    Set Nocount On

    Select
    Case When ISNUMERIC(O_ID)=1
    Then O_ID
    Else Null
    End As O_ID
    From dbo.Orders With(nolock)
    Where (P_ID=@P_ID)

    I need to log the usage of this stored proc in to this table

    CREATE TABLE dbo.ProcedureLog
    (
    LogDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    DatabaseID INT,
    ObjectID INT,
    ProcedureName NVARCHAR(400),
    ErrorLine INT,
    ErrorMessage NVARCHAR(MAX),
    AdditionalInfo NVARCHAR(MAX)
    );
    GO
    CREATE CLUSTERED INDEX cx_LogDate ON dbo.ProcedureLog(LogDate);
    GO


    All i need to do is modify the store proc to log its usage in this table.

    Thanks in Advance,
    Sid

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Create an insert statement to insert a row in to the table and add it to the procedure. You will have to use TRY...CATCH in order to trap any errors.

    We do this sort of thing, however we don't just log sproc execution, we log each stage of the sproc. This is invaluable for debugging. A handy hint to keep your code generic:
    Code:
    SELECT @proc_name = OBJECT_NAME(@@PROCID)
    The person executing needs VIEW_DEFINITION permissions if there are nested sprocs IIRC.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •