Chapter 3 T-SQL Programming

3.1 Using Variable variables is an object that can store data values. You can use local variables to pass data to SQL statements. Many variables can be declared for temporary use when executing a batch of SQL statements in the T-SQL. After declaring a variable, you can set the value of the variable in a T-SQL statement in a batch. The next statement in the batch can retrieve the value from the variable,

3.1 Using Variable variables is an object that can store data values. You can use local variables to pass data to SQL statements. Many variables can be declared for temporary use when executing a batch of SQL statements in the T-SQL. After declaring a variable, you can set the value of the variable in a T-SQL statement in a batch. The next statement in the batch can retrieve the value from the variable,

3.1 Use Variables

A variable is an object that can store data values. You can use local variables to pass data to SQL statements. Many variables can be declared for temporary use when executing a batch of SQL statements in the T-SQL. After declaring a variable, you can set the value of the variable in a T-SQL statement in a batch. The next statement in the batch processing can retrieve the value from the variable and give the result.

Variables in the T-SQL are divided into local variables and global variables. Local variables are declared first and then assigned values. While global variables can be directly used for definition and maintenance of the right system, but generally do not customize global variables.

3.1.1 local variables

The local variable name must be prefixed.

The statement for declaring local variables is as follows:

Declare @ variable_name DataType

Here, variable_name is the name of the local variable, and ype is the data type.

For example:

Declare @ name varchar (8) --- Declare a variable name that stores the name of a student, which can contain a maximum of 8 characters

Declare @ seat int --- Declare a variable seat that stores the student seat number

You can assign values to local variables by using the SET or Select statements.

Syntax:

Set @ variable_name = value

Or

Select @ variable_name = value

Assuming that the insert statement is used, the following test data has been inserted into the stuInfo table.

StuNamestuNostuSexstuAgestuSeatstuAddress

Zhang qiuli s25301 male 181 Beijing Haidian

Li wencai s25302 male 313 Address Unknown

Li Si asked s25303 female 222 Luoyang, Henan Province

Ouyang junxiong s25304 male 284 Xinjiang

Example 1

/* -- Query the information of 'Li wencai --*/

Declare @ name varchar (8) --- Student name

Set @ name = 'Li wencai '--- use set to assign values

Select * from stuInfo where stuName = @ name

/* -- Find the Left and Right deskmates of 'Li wencai --*/

Declare @ seat int --- seat number

Select @ seat = stuSeat from stuInfo where stuName = @ name --- assign values using Select

Select * from stuInfo where (stuSeat = @ seat + 1) or (stuSeat = @ seat-1)

From Example 1, we can see that local variables can be used to pass data in upper and lower statements (such as the seat number @ seat in this example ).

The set Value assignment statement is generally used to assign the specified data constant to the variable, such as "Li wencai" in this example ".

The Select value assignment statement is generally used to query data from a table and then assign it to a variable. Note that the Select statement must ensure that no more than one record is filtered. If more than one record is queried, the value of the last record is assigned to the variable.

3.1.2 global variables

All global variables in SQL Server use two @ signs as the prefix.

For common global variables, see.

Variable meaning

@ ERROR code for the last T-SQL ERROR

@ IDENTITY the last inserted id value

@ LANGUAGE name of the current LANGUAGE

@ MAX_CONNECTIONS the maximum number of simultaneous connections that can be created

@ ROWCOUNT the number of rows affected by the previous SQL statement

@ SERVERNAME name of the local server

@ SERVICENAME name of the SQL service on the computer

@ TIMETICKS the number of points on each scale on the current computer

@ TRANSCOUNT the number of transactions opened by the current connection

@ Versionsql server version

3.2 output statement

The output statements supported in the T-SQL that are used to output the results of the processed data.

The common output statements use the following syntax.

Pint local variable or string

Select Local variable AS Custom column name

The second method is the special application of the query statement.

Example 2

Pint 'server name: '+ @ SERVERNAME

Select @ servername as 'server name'

The results output using the print statement are displayed in text in the message window, and the results output using the Select statement are displayed in a table in the result window.

Because the print statement requires a single local variable or string expression as the parameter, if we write an SQL statement like this, an error will occur.

Print 'current ERROR number' + @ ERROR

Because the global variable @ ERROR returns an integer value. How can this problem be solved? (Conversion Function, which converts a value to a string)

Print 'current ERROR number' + convert (varchar (5), @ ERROR)

After understanding the output statements, let's look at examples of global variables.

Example 3

Insert into stuInfo (stuName, stuNo, stuSex, stuAge) values ('mei chaofeng ', 's25318', 'female ', '23 ')

Print 'current ERROR number' + convert (varchar (5), @ ERROR) --- if it is greater than 0, it indicates that the previous statement has an ERROR.

Print 'The students just registered, seat number: '+ convert (varchar (5), @ IDENTITY)

Update stuInfo set stuAge = 85 where stuName = 'Li wencai'

Print 'current ERROR code: '+ convert (varchar (5), @ ERROR)

Print 'SQL Server version' + @ VERSION

GO

@ ERROR indicates whether the last SQL statement has an ERROR. If an ERROR occurs on a VM in Hong Kong, a non-zero value is returned. The first "insert" statement is correct, so it is 0. The second "Update" Statement violates the age of 15 ~ Because the error code is not zero.

@ IDENTITY can be used to query the last inserted id value (automatically numbered value ). For example, the stuSeat (seat number) field in the stuInfo table in this example is defined as an ID column (automatic label column). The value of this column is automatically generated in Hong Kong Space, therefore, the data of the seat number of the Jumper is not required during insertion. Once the data is inserted, We can query the @ IDENTITY global variable to view the current automatic number value, that is, the seat number of "Mei chaofeng.

3.3 logical control statements

3.3.1 IF-ELSE Condition Statement

Syntax

IF (condition)

Statement or statement Block

ELSE

Statement or statement Block

Same as java, ELSE is optional.

If you have multiple statements, you need to use statement blocks. The statement blocks are represented by BEGIN · END. Their functions are similar to the java language.

IF (condition)

BEGIN

Statement 1

Statement 2

···

END

ELSE

···

Suppose we have added the following test data to the student token table stuMarks.

ExamNostuNowrittenExamLabExam

S271811s253021358

S271813s253025090

S271815s25302650

S271816s253017782

Example 4

DECLARE @ myavg float

Select @ myavg = AVG (writtenExam) FROM stuMarks

Print 'average score of the current shift '+ convert (varchar (5), @ myavg)

IF (@ myavg> 70)

Begin

Print 'excellent test scores of this class, top three :'

Select top 3 * from stuMarks Order by writtenExam DESC

End

Else

Begin

Print 'test scores of this class are poor, and the last three scores are :'

Select top 3 * from stuMarks order by writtenExam

End

To display the output table data and text messages in the same window, you need to make the following settings.