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.
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))AsSet Nocount OnSelectCase When ISNUMERIC(O_ID)=1Then O_IDElse NullEnd As O_IDFrom dbo.Orders With(nolock)Where (P_ID=@P_ID)I need to log the usage of this stored proc in to this tableCREATE 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));GOCREATE CLUSTERED INDEX cx_LogDate ON dbo.ProcedureLog(LogDate);GOAll 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 wantinsert 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 |
|
|
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))AsBeginSet Nocount OnDeclare @ErrLine intDeclare @ErrMessage NVARCHAR(MAX)Begin TrySelectCase When ISNUMERIC(O_ID)=1Then O_IDElse NullEnd As O_IDFrom dbo.Orders With(nolock) --It is not advised to use nolock. Google and you will know the reasonWhere (P_ID=@P_ID)End TryBegin 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,nullEnd |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-27 : 02:07:06
|
ISNUMERIC is not fully reliable. Seeselect isnumeric('2d1'),isnumeric('$'),isnumeric('1e2') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|