//
you're reading...
SQL Server Query

T-SQL (1)

Using Variables

Like C language, variables are divided into:

  • Local variables:
  • Local variables must be marked with @ as a prefix, such asage

    Use of local variables is the first statement, and then the assignment

  • Global variables:
  • Global variables must mark @@ as a prefix, such as @@version

    Global variables, we can only be read by the system definition and maintenance, you can not modify the global value of the variable

Local variables

  • Declare local variables
  • 	DECLARE @ variable name Data type
    	

    For example:

    	DECLAREname varchar (8)
    	DECLAREseat int
    	
  • Assignment
  • 	SET @ variable name = 'value'
    	SELECT @ variable name = 'value'
    	

    For example:

    	SET @ name = 'Joe Smith'
    	SELECT @ name = stuName FROM stuInfo WHERE stuNo = 's25302'
    	

You must be screened to ensure that only one record, otherwise, it is the last one, so the back of T-SQL statements are generally connected WHERE filter.

Question: write T-SQL to find seating around the same table?

Analysis:

First, find the “San” seat number;

The second step, Joe Smith’s seat number plus 1 or minus 1

Examples:

/ * - Find the seating of the information - * /
DECLAREname varchar (8) - Student Name
SET @ name = 'Joe Smith' - to use SET assignment
SELECT * FROM stuInfo WHERE stuName =name
/ * - Find John's around the same table - * /
DECLAREseat int - seat number
SELECT @ seat = stuSeat FROM stuInfo - assignment using SELECT
    WHERE stuName = @ name
SELECT * FROM stuInfo
    WHERE (stuSeat = @ seat + 1) OR (stuSeat = @ seat-1)
GO

Global Variables

global variables prefixed with two @ signs

Variable Meaning
@@ERROR The last T-SQL error error number
@@IDENTITY Last identity value inserted
@@MAX_CONNECTIONS Name of the language currently in use
@@ROWCOUNT The maximum number of simultaneous connections that can be created
@@SERVERNAME The name of the local server
@@TRANSCOUNT The current number of open connections tasks
@@VERSION Version of SQL Server

Examples:

print 'SQL Server versions' +@@VERSION
print 'name of the server:' +@@SERVERNAME

INSERT INTO stuInfo (stuName, stuNo, stuSex, stuAge) VALUES ('Wu', 's25328', 'M', '23 ')

/* If greater than 0 indicates that the previous statement execution error */

print 'current error number' + convert (varchar (5),@@ERROR) print 'Just sign up participants, seat number:' + convert (varchar (5),@@IDENTITY)
			   
UPDATE stuinfo SET stuAge = 85 WHERE stuName = 'Joe Smith'

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

GO
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: