SQL Control Statement Basics

SQL Variable Global variables:

Global variables are defined and maintained by the system using two @ as a prefix and cannot be declared and assigned by the user!

The common global variables are as follows

@ @version: Gets the SQL Server version number currently in use

EG:

Select @ @version

Display information:

--------------------------------------------------------------------------------------------------------------- --------------------------Microsoft SQL Server R2 (RTM)-10.50.1600.1 (X64)
APR 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200:)

@ @identity: The value of the identity column returned by the previous SQL statement

EG:

Insert  into hous (name,price)
 Values (' room one ',299)
 Select @ @IDENTITY   as identity column

Display information:

(1 rows affected)
Identity column
---------------------------------------
5

(1 rows affected)
@ @rowcount: Returns the number of rows affected by the previous SQL statement

Insert  into hous (name,price)
 Values (' room one ',299)
 Select @ @rowcount  as affected row count

Display information:

(1 rows affected)
Number of rows affected
-----------
1

(1 rows affected)

@ @servername: Name of the local server

EG:

Select @ @servername

Display information:

--------------------------------------------------------------------------------------------------------------- -----------------
Desktop-m5olttn

(1 rows affected)

@ @error: The error number of the last SQL error

EG:

Insert  into hous (name,price)
 Values (' room one ')
 Select @ @ERROR  as error number

Show Results:

MSG 109, Level 15, State 1, line 1th
The number of columns in the INSERT statement is greater than the number specified in the VALUES clause. The value in the VALUES clause must match the number of columns specified in the INSERT statement.

Error number
-----------
109

(1 rows affected)

@ @servicename: The name of the SQL Server on this computer

EG:

Select @ @servicename

Show Results:

--------------------------------------------------------------------------------------------------------------- -----------------
MSSQLSERVER

(1 rows affected)

@ @language: The name of the currently used language

EG:

Select @ @language

Show Results:

--------------------------------------------------------------------------------------------------------------- -----------------
Chinese Simplified

(1 rows affected)

@ @max_connections: Maximum number of simultaneous connections that can be created

EG:

Select @ @MAX_CONNECTIONS

Show Results:

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

(1 rows affected)

@ @timeticks: The number of microseconds per tick on the current computer

EG:

Select @ @timeticks

Show Results:

-----------
31250

(1 rows affected)

@ @trancount: Number of transactions opened by the current connection

EG:

Select @ @TRANCOUNT

Show Results:

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

(1 rows affected)

Local variable declaration syntax:

DECLARE @variable datatype

Where variable is the name of the local variable, and datatype is the data type

Assignment Syntax:

Local variables have two ways of replicating

1.set @variable = value

2.select @variable =value

The difference between set and select

Select statements are typically used to assign data from a table to a variable

The SET statement does not support assigning data from a table to a variable

Tips:

Assigning values to a variable is set to be more efficient than select. Set does not support assigning values to multiple variables at the same time

SQL OUTPUT Statements

There are two ways to output in SQL Server

1.print local variable or string

2.select local variable or string

Where the SELECT statement output data is a special use of query statements

Data type conversions

Grammar:

Cast (expression as data type)

Convert (data type, expression ", style")

where "" "is an optional part.

Logical Control Statement Begin-end

Grammar:

Begin

Statement or statement block

End

Similar to "{}" in C #, which represents the fast start and end of a statement. You can include another block of statements in one statement block

If-else and while are similar to C # here no longer one by one description case multi-branch statement

The case multi-branch statement is also similar to the Swith selection structure in C # and is syntactically different

Syntax for case:

Case

When condition 1 then result 1

When condition 2 then result 2

......

"Else Other results"

End

where "" "is an optional part

It is worth noting that the returned result is null if the else is omitted and the When condition is not true

Batch processing-go

It is a collection of one or more SQL statements, that is, an executable unit that can be compiled into a single executable unit to improve the efficiency of execution. If the batch contains multiple SQL statements, all the optimization steps that execute the statements are compiled in a single execution unit

SQL Control Statement Basics