SQL Server常用的全局变量(二)
ed cursor, or the last-opened cursor is closed
www.2cto.com
or deallocated.
n The cursor is fully populated. The value returned (n) is the total number of rows in the cursor.
Examples
This example declares a cursor and uses SELECT to display the value of @@CURSOR_ROWS. The setting has a value of 0 before
the cursor is opened, and a value of -1 to indicate that the cursor keyset is populated asynchronously.
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
www.2cto.com
(1 row(s) affected)
au_lname
----------------------------------------
White
(1 row(s) affected)
-----------
-1
(1 row(s) affected)
4.@@DATEFIRST--针对对话返回SET DATEFIRST的当前值，SET DATEFIRST表示制定每周的第一天
Examples
This example sets the first day of the week to 5 (Friday), and assumes the current day to be Saturday. The SELECT statement
www.2cto.com
returns the DATEFIRST value and the number of the current day of the week.
SET DATEFIRST 5
SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'
Here is the result set. Counting from Friday, today (Saturday) is day 2.
1st Day Today
---------------- --------------
5 2
Examples
This example returns the current timestamp from the pubs database.
USE pubs
SELECT @@DBTS
6.@@ERROR--返回执行上一个transact-SQL语句的错误号，如果前一个transact-SQL语句执行没错误，返回0
Examples
A. Use @@ERROR to detect a specific error
This example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.
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. Use @@ERROR to conditionally exit a procedure
The IF...ELSE statements in this example test @@ERROR after an INSERT statement in a stored procedure. The value of the
@@ERROR variable determines the return code sent to the calling program, indicating success or failure of the procedure.
USE pubs
GO www.2cto.com
-- 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"