Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Stored Proc Log usage help

Author  Topic 

prasadau2006
Starting Member

1 Post

Posted - 2010-11-25 : 19:05:03
HI, I have 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,
Prasad

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-25 : 23:10:02
Just add a

INSERT statement to that table and pass the values you want

insert into dbo.procedurelog(DatabaseID,
ObjectID ,
ProcedureName,
ErrorLine,
ErrorMessage,
AdditionalInfo)
values([whatever you want to insert in the fields],...)



Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-25 : 23:19:06
Try:

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

As
Begin
Set Nocount On

Declare @ErrLine int
Declare @ErrMessage NVARCHAR(MAX)

Begin Try
Select
Case When ISNUMERIC(O_ID)=1
Then O_ID
Else Null
End As O_ID
From dbo.Orders With(nolock) --It is not advised to use nolock. Google and you will know the reason
Where (P_ID=@P_ID)


End Try
Begin Catch
Select @ErrLine =ERROR_LINE() , @ErrMessage= ERROR_MESSAGE()
End Catch

Insert into dbo.ProcedureLog (LogDate,DatabaseID,ObjectID,ProcedureName,ErrorLine,ErrorMessage,AdditionalInfo)
Select getdate(),db_id(),Object_id('Hills_GetO_ID'),'Hills_GetO_ID',@ErrLine,@ErrMessage,null

End
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-27 : 02:07:06
ISNUMERIC is not fully reliable. See


select isnumeric('2d1'),isnumeric('$'),isnumeric('1e2')


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -