SQL Server Stored Procedure return and output parameters and usage tips

Currently, SQL Server is increasingly becoming the most important Database Management System in the operating system of the semi-win7 program. With the launch of SQL Server, microsoft's database service system has truly achieved a unified system of Windows snt/2000 operating systems. on Microsoft's operating system, no database system can compete with it, including Oracle, the leader in the database field. It is undeniable that the biggest defect of SQL Server is that it can only run on Microsoft's own operating system, which is a critical point of SQL Server. But on the other hand, it has become the best accelerator, this prompted SQL Server to exert its functions on its only "land" to maximize the potential of the NT operating system! As a very important concept in the SQL Server database system, the stored procedure can effectively improve the program performance by reasonably using the stored procedure; in addition, encapsulating business logic in the storage process of the database system can greatly improve the maintainability of the entire software system. When your business logic changes, you no longer need to modify and compile client applications and resend them to a large number of users. You only need to modify the storage process on the server side to implement the corresponding business logic. Properly write the stored procedures you need to maximize the use of various SQL Server resources. Next, let's take a look at the experience of writing SQL Server Stored Procedures and using stored procedures. Input this parameter is only used to transmit information from an application to a stored procedure. InputOutput this parameter transfers information from an application to a stored procedure, and transmits information from a stored procedure to a response application. Output this parameter is only used to transmit the response information from the stored procedure. Returnvalue this parameter indicates the return value of the stored procedure. This parameter is not displayed in the Stored Procedure Parameter List of SQL Server. It is only associated with the values in the Return Statement of the stored procedure. After a new value is generated by the primary key in the stored procedure, the Return Statement in the stored procedure is usually used to return the value. Therefore, the type of the parameter used to access this value is returnvalue. 1. If object_id ('up _ user') is not nulldrop proc up_usergocreate proc up_userasset nocount ondelcare @ name varchar (10) beginselect @ name = uname from userendset nocount offgoesec up_user 2. Simple stored procedure with input parameters if object_id ('up _ user ') is not nulldrop proc up_usergocreate proc up_user @ ID intasset nocount ondelcare @ name varchar (10) beginselect @ name = uname from user where uid = @ idendset nocount offgo -- Run the Stored Procedure esec up_user 1 3. Stored Procedure with the return parameter if object_id ('up _ user ') is not nulldrop proc up_usergocreate proc using nocount ondelcare @ age intbeginselect @ age = uage from userreturn @ ageendset nocount offgo -- execute the Stored Procedure declare @ age intexec @ age = up_userselect @ age 4, if object_id ('up _ user ') is not nulldrop proc up_usergocreate proc up_user @ ID int, @ name varchar (10) = ''outputasset nocount Onbeginselect @ name = uname from user where uid = @ idendset nocount offgo -- execute the Stored Procedure declare @ name varchar (10) exec up_user 2, @ name outputselect @ name 5. If exists (Select name from sysobjects where name = 'up _ user' and type = 'P ') drop proc up_usergocreate proc up_user @ ID int, @ name varchar (20) outputasdeclare @ age intbeginselect @ age = stuage, @ name = stuname from stuinfo where uid = @ idretur N @ ageend -- run the Stored Procedure declare @ age intdeclare @ name varchar (20) exec @ age = up_user 2, @ name outputselect @ age, @ name attachment: the global variable @ connections in SQL Server Returns the number of connections or attempts since the last startup. @ Cursor_rows returns the number of qualified rows in the cursor that is last opened on the connection (returns the number of rows of valid data rows that have not been read in the opened cursor) @ datefirst returns the number on the first day of each week @ error returns the error code of the last executed SQL statement. @ Fetch_status returns the status of the last cursor executed by the fetch statement, rather than the status of any cursor currently opened by the connection. @ Identity returns the last inserted Identifier value @ langid returns the local language identifier (ID) of the current language ). @ Language the name of the currently used language is returned. @ Lock_timeout the current lock timeout setting for the current session, in milliseconds. @ Procid returns the ID of the current stored procedure ). @ Rowcount returns the number of rows affected by the previous statement. @ Servername: return the name of the local server that is running. @ Spid returns the server process identifier (ID) of the current user process ). @ Trancount returns the number of active transactions for the current connection. @ Version returns the date, version, and processor type of the current installation. @ Cpu_busy returns the CPU working time since the last time SQL Server was started. Unit: millisecond @ datefirst returns the value of datafirst, which is assigned by the set datefirst command, the command is used to specify the day of the week @ dbts to return the timestamp value of the current database. The timestamp value in the database must be unique @ error. error code @ fetch_status returns the status value of the last fetch statement @ idle returns the duration of CPU being empty and closed since the last time SQL Server was started. Unit: millisecond @ io_busy returns from since the last time SQL Server was started, the CPU took the time to perform the input and output operations, in milliseconds @ langid returns the current language id value @ language returns the current language name @ @ lock_timeout returns the length of time the current session waits for the lock. The unit is millisecond @ max_connections returns the maximum number of connections allowed to connect to SQL Server @ max_precision returns the precision of the decimal and numeric data types @ nestlevel return the initial value of the nested series of the currently executed stored procedure. The initial value is 0 @ options. Return the information of the current set Option @ pack_received. Return the number of input packages read by SQL Server over the network. @ pack_sent returns the SQL statement. number of output packets written by the server to the network @ packet_errors the number of errors returned by the Network Package @ procid returns the id value of the current Stored Procedure @ remserver returns the name of the Remote SQL Server database server @@ servicename returns the service status in which the SQL server is running, for example, MSSQLServer MSDTC SQLServerAgent @ spid. Return the server processing id value processed by the current user @ textsize. Return the textsize option value of the Set statement. The set statement is defined. in the SELECT statement, the maximum length of the text or image data type is the basic unit of byte @ timeticks. The number of microseconds of each clock is returned @ total_errors. The number of disk read/write errors is returned @ total_read. number @ total_write returns the number of disk write operations @ trancount returns the number of activated transactions in the current connection