Common Transact-SQL functions, and transact-SQL Functions
(1) DECLARE:
1>:
DECLARE @ usid VARCHAR (50), @ usna NVARCHAR (100), @ grna NVARCHAR (100 );
2>:
DECLARE @ usid VARCHAR (50)
DECLARE @ usna NVARCHAR (100)
DECLARE @ grna NVARCHAR (100)
(2) select 1 from mytable
Select 1 from mytable; (generally used as a condition)
Select anycol (any row in the target table set) from mytable;
Select * from mytable does not have any difference in function. It is used to check whether there are records.
1 in select 1 from is a constant, and the value of all rows found is it, but in terms of efficiency, 1> anycol> *,
Because you do not need to query dictionary tables.
(3) @ ERROR
Return Value Type: integer
If the execution of the previous Transact-SQL statement is correct, 0 is returned.
If the previous statement encounters an error, the error number is returned.
Because @ ERROR is cleared and reset after each statement is executed, check it immediately after the statement is verified, or save it to a local variable for future viewing.
(4) @ ROWCOUNT
Return Value Type: int
@ ROWCOUNT the value is set to the number of rows affected by the query or read by the query. Example:
Select 1 from mytable
If @ rowcount> 0
....
(5) SET NOCOUNT
The number of rows affected by the Transact-SQL statement;
Set nocount on; no count is returned.
Set nocount off; return count.
If some statements contained in the stored procedure do not return much actual data, this setting greatly reduces network traffic and significantly improves performance.
(6) RAISERROR
The RAISERROR command is used to return user-specified information when the SQL Server System returns an error message.
Syntax:
RAISERROR ({msg_id | msg_str }{, severity, state}
[, Argument [,... n])
[WITH option [,... n]
Parameter description:
Msg_id: User-Defined error information stored in the sysmessages table. The error number of the User-Defined error message must be greater than 50000. The error code generated by special messages is 50,000th.
Msg_str: a special message. Its format is similar to that of PRINTF in C. This error message can contain a maximum of 400 characters. If the information contains more than 400 characters, only the first 397 characters can be displayed and a ellipsis is added to indicate that the information has been truncated. The Standard Message ID of all specific messages is 14000. The formats supported by msg_str are % [[flag] [width] [precision] [{h | l}] type.
Severity: The severity Level of the message association defined by the user. You can use ~ Severity levels between 18. 19 ~ The severity level between 25 can only be used by members of sysadmin fixed server roles. To use ~ Severity Level between 25, WITH option must be set to WTHLOG.
State: from 1 ~ Any integer of 127, indicating the error call status. The default value of state is 1.
Argument: used to replace the variables defined in msg_str or the parameters of messages corresponding to msg_id. There can be 0 or more substitution parameters; however, the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types, such as int1, int2, int4, char, varchar, binary, or varbinary. Other data types are not supported.
WITH option: Incorrect custom option.
That is to say, if your parameter 'System limits you to a minimum of off 'is an error message, and parameter 16 is a severity level, the custom severity level is up to, and the Severity Level of 19-25 is very serious. Parameter 1 is a state and is useless like parameter 5.
The main difference is that the first parameter is useful. You can see at a glance what error messages are reported. The second parameter is also useful, but it is not very useful.
First:
RAISERROR ('Update failed! ', 16,-1)
Second usage:
BEGIN
RAISERROR (N '% s inventory is insufficient, only % d at present, cannot meet the requirements of warehouse picking % d! ', 16, 1, 'memory stick', 15, 20 );
END
Message 50000, level 16, status 1, server TEST_PC \ SQLEXPRESS, 2nd rows memory insufficient, currently only 15, cannot meet the requirements of 20 warehouse picking!
BEGIN
SELECT @ usna = usna FROM usin WHERE usid = @ usid;
SELECT @ grna = grna FROM grin WHERE grid = @ grid;
RAISERROR ('user [% s] ', 16,-1, @ grna, @ usna already exists in position [% s );
RETURN-1
END
