System Variables in sql

System Variables

System variables exist which are automatically determined by SQL Server and do not have to be declared. These are always available and indicate a variety of values.

For example:

@@error                      Error number

@@identity                   Latest identity value of newly inserted record

@@language                Language currently in use

@@max_connections    Maximum connections allowed to the server

@@rowcount                Number of records affected by last command

@@rowcount                Number of rows affected by last statement

@@servername                        SQL Server name

@@version                   Version number of SQL Server

Other system information is returned from scalar functions:

DB_NAME()                  Database Name

SUSER_SNAME()         NT User Name

USER_NAME()              SQL Server User Name

All of these system variables can be used as required within any Transact SQL code as shown in the following example:

CREATE PROCEDURE stpserverinfo AS

select db_name(), user_name(),suser_sname(), @@servername, @@max_connections, @@version, getdate()