将执行存储过程的相关信息( 客户端ip等 )写入到日志表

USE tempdb
GO
--增加操作日志表
IF OBJECT_ID('dbo.operator_log') IS NOT NULL DROP TABLE operator_log
GO
CREATE TABLE dbo.operator_log(
	id INT IDENTITY(1,1) PRIMARY KEY,
	hostName NVARCHAR(50),
	[clientIP] VARCHAR(50),
	[serverIP] VARCHAR(50),
	loginame NVARCHAR(50),
	[program_name] NVARCHAR(200), 
	execTime DATETIME NOT NULL DEFAULT(GETDATE())
)
GO
IF OBJECT_ID('dbo.Proc_Test') IS NOT NULL DROP PROC dbo.Proc_Test
GO
CREATE PROC dbo.Proc_Test
AS
BEGIN
	SET NOCOUNT ON
	--插入到日志表
	INSERT INTO dbo.operator_log
	(
		hostName,
		clientIP,
		serverIP,
		loginame,
		[program_name]
	)
	SELECT  
	s.hostName,
	con.client_net_address AS [clientIP],
	con.local_net_address AS [serverIP],
	s.loginame,
	s.[program_name]
	FROM sys.sysprocesses AS s INNER JOIN sys.dm_exec_connections con ON s.spid=con.session_id AND s.spid=@@SPID
	
	--其它代码
END
GO
EXEC dbo.Proc_Test

SELECT * FROM dbo.operator_log

或者：

USE tempdb
GO
--增加操作日志表
IF OBJECT_ID('dbo.operator_log') IS NOT NULL DROP TABLE operator_log
GO
CREATE TABLE dbo.operator_log(
	id INT IDENTITY(1,1) PRIMARY KEY,
	hostName NVARCHAR(50),
	[clientIP] VARCHAR(50),
	[serverIP] VARCHAR(50),
	loginame NVARCHAR(50),
	[program_name] NVARCHAR(200), 
	execTime DATETIME NOT NULL DEFAULT(GETDATE())
)
GO
IF OBJECT_ID('dbo.Proc_Test') IS NOT NULL DROP PROC dbo.Proc_Test
GO
CREATE PROC dbo.Proc_Test
AS
BEGIN
	SET NOCOUNT ON
	--插入到日志表
	INSERT INTO dbo.operator_log
	(
		hostName,
		clientIP,
		serverIP,
		loginame,
		[program_name]
	)
	SELECT  
	s.[host_name],
	con.client_net_address AS [clientIP],
	con.local_net_address AS [serverIP],
	s.login_name,
	s.[program_name]
	FROM sys.dm_exec_connections AS con INNER JOIN sys.dm_exec_sessions AS s ON con.session_id=s.session_id AND s.session_id=@@SPID
	
	--其它代码
END
GO
EXEC dbo.Proc_Test

SELECT * FROM dbo.operator_log