SQL Server常用的全局变量(五)
ach.
www.2cto.com
CREATE PROCEDURE innerproc as
select @@NESTLEVEL AS 'Inner Level'
GO
CREATE PROCEDURE outerproc as
select @@NESTLEVEL AS 'Outer Level'
EXEC innerproc
GO
EXECUTE outerproc
GO
Here is the result set:
Outer Level
-----------------
1
Inner Level
-----------------
2
17.@@OPTIONS --返回有关当前SET选项的信息
Examples
This example sets NOCOUNT ON and then tests the value of @@OPTIONS. The NOCOUNT ON option prevents the message about the
number of rows affected from being sent back to the requesting client for every statement in a session. The value of
www.2cto.com
@@OPTIONS is set to 512 (0x0200), which represents the NOCOUNT option. This example tests whether the NOCOUNT option is
enabled on the client. For example, it can help track performance differences on a client.
SET NOCOUNT ON
IF @@OPTIONS & 512 > 0
RAISERROR ('Current user has SET NOCOUNT turned on.',1,1)
18.@@PACK_RECEIVED --返回SQL Server自上次启动后从网络读取的输入数据包数
Examples
SELECT @@PACK_RECEIVED
19.@@PACK_SENT --返回SQL Server自上次启动后写入网络读取的输入数据包数
Examples
SELECT @@PACK_SENT
20.@@PACKET_ERRORS --返回自上次启动SQL Server后，在SQL Server连接上的网络数据报错误数
Examples
SELECT @@PACKET_ERRORS
21.@@PROCID --返回Transact-SQL当前模块的对象标识符（ID），Transact-SQL模块可以是存储过程，用户定义函数或触发器
Examples
This example creates a procedure that uses SELECT to display the @@PROCID setting from inside the procedure.
CREATE PROCEDURE testprocedure AS
SELECT @@PROCID AS 'ProcID'
GO
EXEC testprocedure
GO
22.@@REMSERVER --返回远程SQL Server数据库服务器在登录记录中显示的名称
www.2cto.com
Examples
This example creates a procedure, check_server, that returns the name of the remote server.
CREATE PROCEDURE check_server
AS
SELECT @@REMSERVER
The stored procedure is created on SEATTLE1, the local server. The user logs on to a remote server, LONDON2, and runs
check_server.
exec SEATTLE1...check_server
Here is the result set:
---------------
LONDON2
23.@@ROWCOUNT --返回受上一行影响的行数
Examples
This example executes UPDATE and uses @@ROWCOUNT to detect if any rows were changed.
UPDATE authors SET au_lname = 'Jones'
WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
print 'Warning: No rows were updated'
www.2cto.com
24.@@SERVERNAME --返回运行SQL Server的本地服务器的名称
Instance Server information
Default instance 'servername'
Named instance 'servername\instancename'
Virtual server - default instance 'virtualservername'
Virtual server - named instance 'virtualservername\instancename'
Examples
SELECT @@SERVERNAME
25.@@SERVICENAME --返回SQL Server正在其下运行的注册表项的名称，若当前实例为默认实例，则@@SERVICENAME返回MSSQLSERVER
Examples
SELECT @@SERVICENAME
Here is the result set:
------------------------------
MSSQLServer
26.@@SPID --返回当前用户进程的会话ID
Examples
This example returns the process ID, login name, and user name for the current user process.
SELECT @@SPID AS 'ID', SYSTEM_USER AS