Getting Started with SQL Server 7.0 (8)

Returning results in stored procedures
There are three ways to return results from stored procedures:
1. Returning result sets
This is the most common method for client applications to return results. The result set is generated by selecting data using the SELECT statement. Result sets can be generated from permanent tables, temporary tables, or local variables. Returning results to another stored procedure is not an efficient approach. A stored procedure cannot access a result set created by another stored procedure.
For example, return a result set from a permanent table:
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromPermtable
AS
SELECT au_iname FROM authors
GO
For example, create a result set from a local variable:
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromVariable
AS
DECLARE @au_iname char(20)
SELECT @au_iname = au_iname FROM authors
WHERE au_id = '172-32-1176'
SELECT @ au_id
GO
2. Set the value of OUTPUT parameter
Output parameters are often used to retrieve results from stored procedures. If a parameter is defined as OUTPUT when transferred to a stored procedure, any modifications to the parameter will remain in effect after exiting the storage.
For example:
USE pubs
GO
CREATE PROCEDURE ap_SetOutputVar @count integer OUTPUT
AS
SELECT @count = count(*) FROM authors
GO
Retrieve the value from the output parameter:
USE pubs
GO
CREATE PROCEDURE ap_GetOutputVar
AS
DECLARE @num integer
EXECUTE ap_SetOutputVar @num OUTPUT
PRINT “the count is”+convert(char,@num)
GO
· Use cursors as OUTPUT parameters. Cursors can use OUTPUT (output) parameters, but cannot be used as input parameters. In other words, the cursor can be returned as a result, but it cannot be transferred to the procedure. When a cursor is used as a parameter, it needs to be qualified as OUTPUT and VARYING. The VARYING keyword indicates that the result set is to be used to support output parameters. This provides the ability to return a result set to the calling procedure.
For example:
USE pubs
GO
CREATE PROCEDURE GetTitleCount @count_cursor CURSOR VARYING OUTPUT
AS
SET @count_cursor = CURSOR
FOR
SELECT au_id,count(*)
FROM titleauthors
GROUP BY au_id
OPEN @count_cursor
GO
3. Return status through RETURN parameter
This is a method of returning error codes from stored procedures. Stored procedures always return a status value, and users can also use the RETURN statement to return their own status.
For example:
USE pubs
GO
CREATE PROCEDURE ap_SetReturnStatus
AS
DECLARE @count integer
SELECT @count = count(*) FROM authors
IF @count = 0
RETURN (1)
ELSE
RETURN (0)
GO
For example, retrieve the returned status:
USE pubs
GO
CREATE PROCEDURE ap_GetReturnStatus
AS
DECLARE @status integer
EXECUTE @status = ap_SetReturnStatus
IF @status = 1
PRINT “No rows found”
ELSE
PRINT “successful”
GO

Error handling in stored procedures
Like any other program, error handling in stored procedures is very important. System change @@error will get a value after executing each Transact SQL statement. For successful execution, the value of @@error is 0. If an error occurs, @@error will contain error information. The @@error system variable is very important for error handling of stored procedures.
Note: In order to prevent errors, the value that can be set by @@error is reflected in the "error" of the sysmessages table.
There are two types of errors in stored procedures:
1. Database-related errors
These errors are caused by inconsistencies in the database. The system uses non-0 @@error values ​​to represent specific databases. question. After Transact SQL is executed, the error that occurred can be obtained through @@error. If @@error is found to be non-zero, the necessary action must be taken, and in most cases the store will return without further processing. The following example shows a typical method of getting database errors. This procedure places the error code into an output variable so that it can be accessed by the calling program.
USE pubs
GO
CREATE PROCEDURE ap_TrapDatabaseError @return_code integer OUTPUT
AS
UPDATE authors SET au_iname = “Jackson”
WHERE au_iname = “Smith”
IF @@error <> 0
BEGIN
SELECT @return_code = @@error
RETURN
END
ELSE
@return_code = 0
GO
2, Business logic error
These errors are caused by business rule violations. To get these errors, you first need to define business rules. Based on these rules, you need to add the necessary error detection code to the stored procedure. People often use the RAISERROR statement to report these errors. RAISERROR provides the ability to return user-defined errors and set the @@error variable to a user-defined error number. Error messages can be created dynamically or retrieved from the "sysmessages" table based on the error number. Once an error occurs, the error is returned to the client in the form of a server error message. The following is the syntax of the RAISERROR command:
RAISERROR (msg_id | msg_str, severity, state
[, argument ][,...n]])
[WITH options]
Msg_id specifies the id of the user-defined message, which is stored in "sysmessages" in system tables.
Msg_str is the message string used to dynamically create messages. This is very similar to "printf" in C language.
Severity defines the severity of the error message assigned by the user.
State is any integer value from 1 to 127, which represents incorrect call status information. Negative state values ​​will default to 1.
OPTIONS indicates incorrect customization options. The valid values ​​of OPTIONS are as follows:
1) LOG.
Log errors to the server error log and NT event log. This option requires messages with a severity from 19 to 25. Only system administrators can issue such messages.
2) NOWAIT.
Send messages to client server immediately.
3) SETERROR.
Set the value of @@error to msg_id or 5000 regardless of its severity level.

Remote Procedure Call
    SQL Server provides the ability to call stored procedures that reside on different servers. Calling such a stored procedure is called a remote stored procedure call. In order for calls to be transferred from one SQL Server to another, the two servers should be defined as effective remote servers for each other.
                                                                                                                                                using – being being extended the group of a certain server, etc.
         · Right-click on the server and click "Properties".
        · Set the option "Allow other SQL Servers to connect remotely to this SQL server via RPC".
         · Set the value of the "Query time out" option, which specifies the number of seconds to wait for a return from a query processing. The default value is 0, which means unlimited waiting time is allowed.
       · After setting the configuration options, click "OK".
· The changes will take effect after restarting the server.
· Repeat the same steps on the other remote server.
              To call a remote stored procedure, you need to specify the name of the server, followed by the name of the database and the name of the owner. Below is an example of calling a stored procedure on a different server (Server2).
Exec server2.pubs.dbo.myproc

Doudou’s remarks:
This is only a superficial introduction to common knowledge of SQL Server. It is also intended for programmers who write applications based on SQL Server databases, not database managers. But for application programmers, understanding database management is also very useful. It is recommended that you learn about database management by yourself in the future, which is also very useful for optimizing programs.