MSSQLServer database-variable and Batch Processing

As a learning summary! The following content has been written a long time ago. But I still need to write it again. First, I want to write it myself. Article The second is to learn more about knowledge. The SQL language is similar to other Programming Language It also has control statements such as variables, branches, and loops. I didn't know which term I should use until the last night I saw an article about SQL statement batch processing, all the control statements in SQL, such as variables, branches, and loops, are processed in batches. In SQL, variables are divided into local variables and global variables. global variables are also called system variables. Let's take a look at the local variables. : Declare local variables with the declare keyword. The syntax is very simple: declare @ <variable name> <variable type> [, @ <variable name> <variable type> [...] from the above [, @ <variable name> <variable type> [...] here we can see that he can declare multiple variables at a time. The value of the variable is null before it is declared but not assigned a value. You can assign values to variables using Set Keyword. When the set keyword is used, only one value can be assigned to the variable at a time. Let's look at an example:

Declare @ name nvarchar (50), @ age intprint @ name; print @ age; print '-------------- '; Set @ name = 'jack' set @ age = 18 print @ name; print @ age; print '--------------'; -- The result shows: ---------------- jack18 ----------------
 

We can see that the output is null before the value is assigned. If you use the set keyword to assign only one value to a variable at a time, how can you assign values to multiple variables? You can useSelectKeywords

 
Declare @ name nvarchar (50), @ age intselect @ name = 'jack', @ age = 18 print @ name; print @ age; print '----------------';
 

We can also assign values to this variable in the query statement, for example: Select @ name = sname from student where Sid = 1;

 

Let's take a look at global variables.

SQL Server has more than 30 Global System variables, but several are commonly used. The following SQL statements

Select @ version -- view the version number select @ identity; -- return the last inserted id value. For example, use myschoolcreate table idetest (ID int identity () primary key, idename nvarchar (50),) insert into idetest (idename) values ('test1'); insert into idetest (idename) values ('test2'); insert into idetest (idename) values ('test3'); insert into idetest (idename) values ('test4'); insert into idetest (idename) values ('test5'); insert into idetest (idename ) Values ('test6') Select @ identity; select @ error -- returns the error number. When the previous SQL statement is incorrect, I guess when its value is greater, indicates an error. If no error occurs, select @ language is used; -- Return version language select @ servername -- return server name select @ rowcount -- returns the number of rows affected by the previous statement -- for example, the result shows Delete idetest where ID <= 3 select @ @ rowcount
 

 

Scripts in SQL statements

The script provides control statements such as variables, branches, and loops to implement complex tasks. The flow control statements in SQL statements include:

• If... Else
• Goto
• While
• Waitfor
• Try/catch
• Case

1. Let's take a look at the IF... else statement. The syntax format is as follows:

If<Boolean expression><SQL statement>|

Begin<Code Series>

End[Else <SQL statement> |

Begin <code series>

End]

Note: UseCodeDon't forget begin and end. Here, bengin... end is equivalent to {} in C. In addition, there is a trap for if judgment, that is, if @ Var = NULL. this statement is incorrect because null is not equal to anything, or even null. It should be written as if @ VaR is null. Let's take a look at the specific example:

 Use myschoolcreate table score (ID int identity (1, 1) primary key, sname nvarchar (50 ), english int, math int, computer INT) insert into score (sname, English, math, computer) values ('jack', 50, 60, 90); insert into score (sname, English, math, computer) values ('loud', 100,); insert into score (sname, English, math, computer) values ('andy ', 66 ); insert into score (sname, English, math, computer) values ('am', 55); insert into score (sname, English, math, computer) values ('jobs', 34,61, 44); insert into score (sname, English, math, computer) values ('bill ', 54,32, 76); insert into score (sname, english, math, computer) values ('afs ', 72); -- view the English score. When the average score of English is less than the score of three people with the lowest score, declare @ AVG floatselect @ AVG = AVG (English) from scoreif (@ AVG <60) begin select top 3 * From score order by English ASC endelse begin select top 3 * From score order by English desc end 

If... elseif... else is not found in SQL, how can we implement the function of type if... elseif... else? The following is an example.

Use myschoolcreate table score (ID int identity (1, 1) primary key, sname nvarchar (50), English int, math int, computer INT) insert into score (sname, English, math, computer) values ('jack', 60, 60, 90); insert into score (sname, English, math, computer) values ('lou ', 100 ); insert into score (sname, English, math, computer) values ('andy ', 66); insert into score (sname, English, math, computer) values ('am', 55); insert into score (sname, English, math, computer) values ('jobs', 44); insert into score (sname, english, math, computer) values ('bill ', 76); insert into score (sname, English, math, computer) values ('afs', 72 ); declare @ AVG floatselect @ AVG = English from scoreif (@ AVG <60) begin select top 3 * From score order by English ASC endelse begin if (@ AVG> 60 and @ AVG <70) begin select * From score where English> 60 order by English end else begin select top 3 * From score order by English desc end

2. While statement

Let's take a look at the following example:

 
Declare @ I intset @ I = 0 while (@ I <1000) Begin set @ I = @ I + 1 insert into score (sname, English, math, computer) values ('test' + convert (nvarchar (10), @ I), @ I % 60, @ I % 60, @ I % 60) end to do an exercise, if the number of people who fail is more than half, 2 points will be given to each person. Select @ COUNT = count (*) from Score select @ failcount = count (*) from Score where English <60 while (@ failcount> @ count/2) begin update score set English = English + 2 -- in order to jump out of the loop, after adding one, check whether the number of students exceeds half. -- set @ failcount =-1 select @ failcount = count (*) from Score where English <60 end
 

3. Case... end

The syntax format of case... end is as follows:

Case expression

When value1 then returnvalue1 when value2 then returnvalue2 else defaultreturn value end we use the SQL code example to deepen our understanding of adding case... end:

-- Look for the student table. When the age is 19, the young people are displayed. When the age is 18, the young people are displayed. If the age is other, the Select sage is displayed, case sage when '19' then' youth 'when' 18 'then' kiddies 'else' teenagers 'end from student -- case .. end can be used at the customer level and administrator level. For example, if the customer level is 1, it indicates a common user, and if the customer level is 2, it indicates a VIP user-one of the cases can generate a column, multiple cases will generate multiple columns-for example, a, showing the employee name, age, and salary-the salary level is between 1-5 K, the salary level is 5-10 k so intermediate, and the job level is 10 K or above. Select fname, Fage, (Case fsalary when fsalary> 1000 and fsalary <= 5000 then 'beginner 'when fsalary> 5000 and fsalary <= 10000 then' intermediate 'when fsalary> 10000 then' advanced 'else' not rating ') as 'salary level' from table -- there are two tables as follows: how to use SQL statements to implement the second table to display the content of the first table -- the first table: name subject result Zhang San Language 80 Zhang San mathematics 90 Zhang San English 100 Li Si language 77 Li Si mathematics 88 Li Si English 99-the second table: name Chinese Mathematics English Li Si 77 88 99 Zhang San 80 90 100 -- SQL statement: select [name] sum (case subject when 'China' then result end) as 'language 'sum (case subject when' math 'then result end) as 'math 'sum (case subject when' 'Then result end) as 'English 'group by [name]

Waitfor statement The waitfor statement indicates a time wait. Syntax: waitfor delay <'time'> | time <'time'>
The delay parameter specifies the amount of waiting time, not the number of days, but only the hour, minute, and second. The maximum latency is 24 hours,

For example: Waitfordelay '00: 01'

Select * From Score

The time parameter is specified to wait until a specific time in a day. It can also be in the 24-hour format:WaitforTime'01:00'; Wait until today to execute the following statement

 

We recommend two reference blog posts:

Http://www.cnblogs.com/30ErLi/archive/2010/09/17/1829274.html

Some of this article is copied from here. There is also an article about sqlcmd used in batch processing:

Http://blog.163.com/llsh2010@126/blog/static/1691895692011615113838734/