Use transactions and return values in SQL Server Stored Procedures

1 create procedure testtran
2
3
4
5 declare @ userid int
6
7 set nocount on
8
9 begin Tran adduser
10
11 insert into testtable (username, password, email) values ('milo', '000000', 'milo42102126. com ')
12
13 if @ error <> 0 or @ rowcount = 0 goto errmsg
14
15 set @ userid = @ identity
16
17 set nocount off
18
19 commit Tran adduser
20
21 return 1 -- added successfully
22
23
24 errmsg:
25 set nocount off
26 rollback Tran adduser
27 return-1 -- failed to add and roll back
28
29
30 go
31

Set nocount on optimized storage process

Client Applications are useless. This information is the done_in_proc information of each statement in the stored procedure.

We can use set nocount to control this information to improve program performance.

The help in msdn is as follows:

Set nocount
The returned results do not contain information about the number of rows affected by the transact-SQL statement.

Syntax
Set nocount {on | off}

Note
When set nocount is on, no count is returned (indicating the number of rows affected by the transact-SQL statement ). When set nocount is off, return the count.

Even if set nocount is on, the @ rowcount function is updated.

When set nocount is on, the done_in_proc information of each statement in the stored procedure is not sent to the client. When you use a utility provided by Microsoft SQL server to execute a query, you can run the following statements in the transact-SQL statement (such as select, _ insert, _ update, and _ delete) "Nn rows affected" is not displayed in the query results at the end ".

If some statements contained in the stored procedure do not return much actual data, this setting greatly reduces network traffic and significantly improves performance.

 

When the query analyzer executes the update or delete statement, it will prompt that xx rows of records are affected,
When set nocount is on, this prompt is not returned after the related operation is executed (this information is sometimes not required according to the actual situation). This setting reduces network traffic and improves efficiency.
On and off: in fact, it is a set of switches. When it is set to on, no result lines are displayed for all subsequent SQL statements. When it is set to off, It is restored to the original state. 1
2 Use pubs
3 Update DBO. Titles set title_id = ltrim (title_id
4 -- execute the preceding SQL display (the affected rows are 18 rows)
5 update DBO. Titles set Title = ltrim (title)
6 -- (the number of affected rows is 18)
7
8 set nocount on
9 update DBO. Titles set title_id = ltrim (title_id)
10 -- the command has been successfully completed. (No rows are prompted)
No number of rows will be prompted when any SQL statement is executed later than 11, unless set nocount off
12

Set nocount is set during execution or runtime, rather than during analysis. (This attribute cannot be set in the programming stage. It must be set using statements)

Permission
The Set nocount permission is granted to all users by default.

Conclusion: Set nocount on should be added to the header of the stored procedure, and set nocount off should be added when the stored procedure is exited (there is no need to write, but it is just a habit of writing, some settings need to be disabled after they are enabled in the stored procedure, without affecting other statements) to optimize the stored procedure.

Let's say two more words:

1: when viewing sqlserver's help, pay attention to the "permission" section, because some statements require certain permissions, which are often ignored.

2: @ rowcount refers to the number of rows affected by the previous statement, including the number of records found, the number of deleted rows, and the number of updated records, and @ rowcount should follow the statement to be judged; otherwise, @ rowcount will return 0.

3: If you use a table variable, you must use an alias in the conditional expression to represent the name. Otherwise, an error is reported.

4: transactions must be processed in cud operations.

5: Use an error handler to check that the T-SQL Statement (IF) of the @ error system function is actually clearing the @ error value in the process, no value except zero can be captured, you must use set or select to capture error codes immediately.