as with the C language, variables are divided into: Q local variables: Q Local variables must be marked@ as prefixSuch as@agethe use of the Q local variable is also declared first, then the Q global variable: Q global variable must be marked @@ as prefixSuch as@ @versionq Global variables are defined and maintained by the system, we can only read, cannot modify the values of global variables local variables q declares local variablesDECLARE @ variable namedata types such as:DECLARE @name varchar(8)DECLARE @seat intQ Assign ValueSET @ variable name =valueSELECT @ variable name =values such as:SET @name=' Zhang San 'SELECT @name=Stuname fromStuinfoWHEREStuno=' s25302 ' must ensure that only 1 records are filtered out, otherwise the last one is taken, so the T-SQL statement is generally followed by where filter conditions. Problem: Writing T-SQL Find Zhang San's left and right deskmate? Analysis: The first step, find the "Zhang San" seat number, the second step, Zhang San seat number plus 1 or minus 1 example:/*-- Find the information of Zhang San--*/DECLARE @name varchar(8)--Student NameSET @name=' Zhang San '--assigning values using SetSELECT * fromStuinfoWHEREStuname= @name/*-- find Zhang San's right and left deskmate--*/DECLARE @seat int --Seat numberSELECT @seat=Stuseat fromStuinfo--Assigning values using select WHEREStuname=@nameSELECT * fromStuinfoWHERE(Stuseat= @seat+1)OR(Stuseat= @seat-1)GOGlobal variables Global variables are used with two@ flag as Prefix
Variable meaning@ @ERRORlast one T-error number for SQL error@ @IDENTITYlast-inserted identity value@ @LANGUAGEThe name of the currently used language@ @MAX_CONNECTIONSmaximum number of simultaneous connections that can be created@ @ROWCOUNTnumber of rows affected by the previous SQL statement@ @SERVERNAMEname of the local server@ @TRANSCOUNTnumber of transactions opened by the current connection@ @VERSIONThe version information for SQL Server focuses on the global variables identified by the thick line, and the others can be queried in SQL Server Help. Example:Print' version of SQL Server '+@ @VERSIONPrint' Name of server: '+@ @SERVERNAMEINSERT intostuinfo (stuname,stuno,stusex,stuage)VALUES(' Wu Song ', ' s25328 ', ' Male ', ' at')--if greater than 0 indicates that the previous statement was executed with an errorPrint' Current error number '+Convert(varchar(5),@ @ERROR) Printthe student who has just enrolled, the seat number is: '+Convert(varchar(5),@ @IDENTITY )UPDATEStuinfoSETStuage= - WHEREStuname=' Zhang San 'Print' Current error number '+Convert(varchar(5),@ @ERROR)GO
T-SQL (1)-Variable (RUI)