SQL SERVER 2005系统变量整理

一直做T-SQL开发,在开发过程中经常会用到一些系统变量,今天抽个时间整理一下。[@more@]

@@CONNECTIONS
返回自上次启动 Microsoft? SQL Server? 以来连接或试图连接的次数。

语法
@@CONNECTIONS

返回类型
integer

注释
连接与用户不同。例如，应用程序可以打开多个与 SQL Server 的连接，而不需要用户监视这些连接。

若要显示一个包含几个 SQL Server 统计信息的报表，包括试图连接统计信息，请运行 sp_monitor。

示例
下面的示例显示了到当前日期和时间为止试图登录的次数。

SELECT GETDATE() AS 'Today's Date and Time',
@@CONNECTIONS AS 'Login Attempts'

下面是结果集：

Today's Date and Time Login Attempts
--------------------------- ---------------
1998-04-09 14:28:46.940 18

@@CURSOR_ROWS
返回连接上最后打开的游标中当前存在的合格行的数量。为提高性能，Microsoft? SQL Server? 可以异步填充大键集和静态游标。可调用 @@CURSOR_ROWS，以确定当它被调用时，符合游标的行的数目被进行了检索。

返回值 描述
-m 游标被异步填充。返回值 (-m) 是键集中当前的行数。
-1 游标为动态。因为动态游标可反映所有更改，所以符合游标的行数不断变化。因而永远不能确定地说所有符合条件的行均已检索到。
0 没有被打开的游标，没有符合最后打开的游标的行，或最后打开的游标已被关闭或被释放。
n 游标已完全填充。返回值 (n) 是在游标中的总行数。

语法
@@CURSOR_ROWS

返回类型
integer

注释
若最后打开的游标是异步打开的，则 @@CURSOR_ROWS 返回的值是负数。若 sp_configure cursor threshold 的值大于0，则键集驱动程序或静态游标被异步打开，且游标结果集中的行数大于游标阈值。

示例
下面的示例声明了一个游标，并且用 SELECT 显示 @@CURSOR_ROWS 的值。在游标打开前，设置值为 0，值 -1 则表示游标键集被异步填充。

SELECT @@CURSOR_ROWS
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
SELECT @@CURSOR_ROWS
CLOSE authors_cursor
DEALLOCATE authors_cursor

-----------
0

(1 row(s) affected)

au_lname
----------------------------------------
White

(1 row(s) affected)

-----------
-1

(1 row(s) affected)

@@DATEFIRST
返回 SET DATEFIRST 参数的当前值，SET DATEFIRST 参数指明所规定的每周第一天：1 对应星期一，2 对应星期二，依次类推，用 7 对应星期日。

语法
@@DATEFIRST

返回类型
tinyint

注释
美国英语中默认 7 对应星期日。

示例
下面的示例将每周第一天设为 5 （星期五），并假定当日是星期六。SELECT 语句返回 DATEFIRST 值和当日是此周的第几天。

SET DATEFIRST 5
SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'

下面是结果集。从星期五算起，今天（星期六）是第二天。

1st Day Today
---------------- --------------
5 2

@@ERROR
返回最后执行的 Transact-SQL 语句的错误代码。

语法
@@ERROR

返回类型
integer

注释
当 Microsoft? SQL Server? 完成 Transact-SQL 语句的执行时，如果语句执行成功，则 @@ERROR 设置为 0。若出现一个错误，则返回一条错误信息。@@ERROR 返回此错误信息代码，直到另一条 Transact-SQL 语句被执行。您可以在 sysmessages 系统表中查看与 @@ERROR 错误代码相关的文本信息。

由于 @@ERROR 在每一条语句执行后被清除并且重置，应在语句验证后立即检查它，或将其保存到一个局部变量中以备事后查看。

示例
A.用 @@ERROR 检测一个特定错误
下面的示例用 @@ERROR 在一个 UPDATE 语句中检测限制检查冲突（错误 #547）。

USE pubs
GO
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = "172-32-1176"

IF @@ERROR = 547
print "A check constraint violation occurred"

B.用 @@ERROR 有条件地退出一个过程
在此示例中，IF...ELSE 语句在存储过程中的 INSERT 语句后检测 @@ERROR。@@ERROR 变量的值将决定传给调用程序的返回值，以指示此过程的成功与失败。

USE pubs
GO

-- Create the procedure.
CREATE PROCEDURE add_author
@au_id varchar(11),@au_lname varchar(40),
@au_fname varchar(20),@phone char(12),
@address varchar(40) = NULL,@city varchar(20) = NULL,
@state char(2) = NULL,@zip char(5) = NULL,
@contract bit = NULL
AS

-- Execute the INSERT statement.
INSERT INTO authors
(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract) values
(@au_id,@au_lname,@au_fname,@phone,@address,
@city,@state,@zip,@contract)

-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT "An error occurred loading the new author information"
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT "The new author information has been loaded"
RETURN(0)
END
GO

C.用 @@ERROR 检测几条语句的成功
下面的示例取决于 INSERT 和 DELETE 语句的成功操作。局部变量在两条语句后均被设置为 @@ERROR 的值，并且用于此操作的共享错误处理例程中。

USE pubs
GO
DECLARE @del_error int, @ins_error int
-- Start a transaction.
BEGIN TRAN

-- Execute the DELETE statement.
DELETE authors
WHERE au_id = '409-56-7088'

-- Set a variable to the error value for
-- the DELETE statement.
SELECT @del_error = @@ERROR

-- Execute the INSERT statement.
INSERT authors
VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
'6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for
-- the INSERT statement.
SELECT @ins_error = @@ERROR

-- Test the error values.
IF @del_error = 0 AND @ins_error = 0
BEGIN
-- Success. Commit the transaction.
PRINT "The author information has been replaced"
COMMIT TRAN
END
ELSE
BEGIN
-- An error occurred. Indicate which operation(s) failed
-- and roll back the transaction.
IF @del_error <> 0
PRINT "An error occurred during execution of the DELETE
statement."

IF @ins_error <> 0
PRINT "An error occurred during execution of the INSERT
statement."

ROLLBACK TRAN
END
GO

D. 与 @@ROWCOUNT 一同使用 @@ERROR
下面的示例用 @@ERROR 和 @@ROWCOUNT 验证一条 UPDATE 语句的操作。为任何可能出现的错误而检验 @@ERROR 的值，而用 @@ROWCOUNT 保证更新已成功应用于表中的某行。

USE pubs
GO
CREATE PROCEDURE change_publisher
@title_id tid,
@new_pub_id char(4)
AS

-- Declare variables used in error checking.
DECLARE @error_var int, @rowcount_var int

-- Execute the UPDATE statement.
UPDATE titles SET pub_id = @new_pub_id
WHERE title_id = @title_id

-- Save the @@ERROR and @@ROWCOUNT values in local
-- variables before they are cleared.
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT

-- Check for errors. If an invalid @new_pub_id was specified
-- the UPDATE statement returns a foreign-key violation error #547.
IF @error_var <> 0
BEGIN
IF @error_var = 547
BEGIN
PRINT "ERROR: Invalid ID specified for new publisher"
RETURN(1)
END
ELSE
BEGIN
PRINT "ERROR: Unhandled error occurred"
RETURN(2)
END
END

-- Check the rowcount. @rowcount_var is set to 0
-- if an invalid @title_id was specified.
IF @rowcount_var = 0
BEGIN
PRINT "Warning: The title_id specified is not valid"
RETURN(1)
END
ELSE
BEGIN
PRINT "The book has been updated with the new publisher"
RETURN(0)
END
GO

@@FETCH_STATUS
返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。

返回值 描述
0 FETCH 语句成功。
-1 FETCH 语句失败或此行不在结果集中。
-2 被提取的行不存在。

语法
@@FETCH_STATUS

返回类型
integer

注释
由于 @@FETCH_STATUS 对于在一个连接上的所有游标是全局性的，要小心使用 @@FETCH_STATUS 。在执行一条 FETCH 语句后，必须在对另一游标执行另一 FETCH 语句前测试 @@FETCH_STATUS 。在任何提取操作出现在此连接上前，@@FETCH_STATUS 的值没有定义。

例如，用户从一个游标执行一条 FETCH 语句，然后调用一个存储过程，此存储过程打开并处理另一个游标的结果。当控制从被调用的存储过程返回后，@@FETCH_STATUS 反映的是在存储过程中执行的最后的 FETCH 语句的结果，而不是在存储过程被调用之前的 FETCH 语句的结果。

示例
下面的示例用 @@FETCH_STATUS 控制在一个 WHILE 循环中的游标活动。

DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

@@IDENTITY
返回最后插入的标识值。

语法
@@IDENTITY

返回类型
numeric

注释
在一条 INSERT、SELECT INTO 或大容量复制语句完成后，@@IDENTITY 中包含此语句产生的最后的标识值。若此语句没有影响任何有标识列的表，则 @@IDENTITY 返回 NULL。若插入了多个行，则会产生多个标识值，@@IDENTITY 返回最后产生的标识值。如果此语句激发一个或多个执行产生标识值的插入操作的触发器，则语句执行后立即调用 @@IDENTITY 将返回由触发器产生的最后的标识值。若 INSERT 或 SELECT INTO 语句失败或大容量复制失败，或事务被回滚，则 @@IDENTITY 值不会还原为以前的设置。

在返回插入到表的 @@IDENTITY 列的最后一个值方面，@@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 函数类似。

@@IDENTITY 和 SCOPE_IDENTITY 将返回在当前会话的所有表中生成的最后一个标识值。但是，SCOPE_IDENTITY 只在当前作用域内返回值，而 @@IDENTITY 不限于特定的作用域。

IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回任何会话和任何作用域中为特定表生成的标识值。有关更多信息，请参见 IDENT_CURRENT。

示例
下面的示例向带有标识列的表中插入一行，并用 @@IDENTITY 显示在新行中使用的标识值。

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

@@LANGID
返回当前所使用语言的本地语言标识符(ID)。

语法
@@LANGID

返回类型
smallint

注释
若要查看语言设置信息（包括语言 ID 号），可不带参数运行 sp_helplanguage。

示例
下面的示例将当前会话的语言设置为意大利语 (Italian)，然后用 @@LANGID 返回意大利语的 ID。

SET LANGUAGE 'Italian'
SELECT @@LANGID AS 'Language ID'

下面是结果集：

Language ID
--------------------
6

@@LANGUAGE
返回当前使用的语言名。

语法
@@LANGUAGE

返回类型
nvarchar

注释
若要查看语言设置信息（包括合法的官方语言名），可不带参数运行 sp_helplanguage。

示例
下面的示例返回当前会话的语言。

SELECT @@LANGUAGE AS 'Language Name'

下面是结果集：

Language Name
-----------------------------
us_english

@@LOCK_TIMEOUT
返回当前会话的当前锁超时设置，单位为毫秒。

语法
@@LOCK_TIMEOUT

返回类型
integer

注释
SET LOCK_TIMEOUT 允许应用程序设置语句等待阻塞资源的最长时间。当一条语句已等待超过 LOCK_TIMEOUT所设置的时间，则被锁住的语句将自动取消，并给应用程序返回一条错误信息。

在一个连接的开始，@@LOCK_TIMEOUT 返回一个 –1值。

示例
下面的示例显示当一个 LOCK_TIMEOUT 值未被设置时的结果集。

SELECT @@LOCK_TIMEOUT

下面是结果集：

----------------
-1

下面的示例设置 LOCK_TIMEOUT 为 1800 毫秒，然后调用 @@LOCK_TIMEOUT。

SET LOCK_TIMEOUT 1800
SELECT @@LOCK_TIMEOUT

下面是结果集：

------------------------------
1800

@@MAX_CONNECTIONS
返回 Microsoft? SQL Server? 上允许的同时用户连接的最大数。返回的数不必为当前配置的数值。

语法
@@MAX_CONNECTIONS

返回类型
integer

注释
实际允许的用户连接数也依赖于所安装 SQL Server 的版本以及应用程序和硬件的限制。

若要将 SQL Server 重新配置为更少的连接，应使用 sp_configure。

示例
下面的示例假定 SQL Server 尚未被重新配置更少的用户连接。

SELECT @@MAX_CONNECTIONS

下面是结果集：

------------------
32767

@@MAX_PRECISION
返回 decimal 和 numeric 数据类型所用的精度级别，即该服务器中当前设置的精度。

语法
@@MAX_PRECISION

返回类型
tinyint

注释
默认情况下，最大精度返回 38。

示例
SELECT @@MAX_PRECISION

@@PROCID
返回当前过程的存储过程标识符 (ID) 。

语法
@@PROCID

返回类型
integer

示例
下面的示例创建了一个过程，在此过程内用 SELECT 显示 @@PROCID 设置。

CREATE PROCEDURE testprocedure AS
SELECT @@PROCID AS 'ProcID'
GO
EXEC testprocedure
GO

@@ROWCOUNT
返回受上一语句影响的行数。

语法
@@ROWCOUNT

返回类型
integer

注释
任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。

示例
下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。

UPDATE authors SET au_lname = 'Jones'
WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
print 'Warning: No rows were updated'

@@TRANCOUNT
返回当前连接的活动事务数。

语法
@@TRANCOUNT

返回类型
integer

注释
BEGIN TRANSACTION 语句使 @@TRANCOUNT 递增 1。ROLLBACK TRANSACTION 将 @@TRANCOUNT 递减为 0，但 ROLLBACK TRANSACTION savepoint_name 语句并不影响 @@TRANCOUNT 值。COMMIT TRANSACTION 或 COMMIT WORK 将 @@TRANCOUNT 递减 1。

示例
下面的示例用 @@TRANCOUNT 测试应该提交的打开事务。

BEGIN TRANSACTION
UPDATE authors SET au_lname = upper(au_lname)
WHERE au_lname = 'White'
IF @@ROWCOUNT = 2
COMMIT TRAN

IF @@TRANCOUNT > 0
BEGIN
PRINT 'A transaction needs to be rolled back'
ROLLBACK TRAN
END

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7757832/viewspace-933913/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7757832/viewspace-933913/