Getting Started with SQL Server 7.0 (eight)---return results in stored procedures

server| stored procedure stored procedure return result
There are three ways to return results from a stored procedure:
1. Return result set
This is the most common way for a client application to return results. The result set is produced by selecting the data by using the SELECT statement. The result set can be generated from a permanent table, a temporary table, or a local variable. Returning the results to another stored procedure is not an effective method. A stored procedure cannot access a result set established by another stored procedure.
For example, to 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 output parameter value
Output parameters are often used to retrieve results from a stored procedure. If a parameter is defined as output when it is transferred to a stored procedure, any modifications to the parameter are still valid after exiting the store.
For example:
Use pubs
Go
CREATE PROCEDURE ap_setoutputvar @count integer OUTPUT
As
SELECT @count = count (*) from authors
Go
To retrieve a value from an 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 the cursor as an output parameter. Cursors can use output (output) parameters, but cannot be used as input parameters. That is, the cursor can be returned as a result, but it cannot be transferred to the process. When a cursor is used as a parameter, it needs to be qualified for 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 the 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 the status by returning parameter
This is a method of returning an error code from a stored procedure. The stored procedure always returns a status value, and the user can return to his or her state by using the returns statement.
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
As with other programs, it is important to make error handling in stored procedures. System Change @ @error will get a value after executing every Transact SQL statement. For a successful execution, the @ @error value is 0, and if an error occurs, the @ @error contains the error message. @ @error System variables are important for error handling of stored procedures.
Note: To prevent errors, the value that @ @error can set 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 database inconsistencies, and the system uses @ @error values other than 0 to indicate a specific database problem. After Transact SQL execution, you can get the error that occurred through the @ @error. If you find that @ @error is not 0, you must take the necessary action and, in most cases, the store will no longer continue processing and return. The following example shows a typical way to get a database error. This procedure places the error code in the output variable so that the calling program can access it.
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 a violation of the business rules. To get these errors, you first need to define the business rules, and based on these rules, you need to add the necessary error detection code to the stored procedure. People often use RAISERROR statements to communicate these errors. RAISERROR provides the ability to return user-defined errors and set the @ @error variable to a user-defined error number. The error message can be dynamically established or retrieved from the "sysmessages" table based on the error number. If an error occurs, the error is returned to the client in the form of a server error message. The following is the syntax for the RAISERROR command:
RAISERROR (msg_id | msg_str, severity, state
[, Argument] [,... N]])
[With options]
MSG_ID indicates the ID of the user-defined message, which is stored in the sysmessages system table.
MSG_STR the message string used to dynamically create a message. This is very similar to "printf" in C language.
Severity defines the severity of the error message that the user assigns.
State is any integer value from 1 through 127 that represents the wrong invocation status information. The state value of a negative number defaults to 1.
Options indicate the wrong customization options. The available values for options are as follows:
1) LOG.
Log errors to the server error log and NT event log. This option requires a message with a severity from 19 to 25. Only the system administrator can send this message.
2) nowait.
Sends the message to the 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 invoke stored procedures that reside on different servers. Call such a stored procedure call a remote stored procedure invocation. To allow the call to be transferred from one SQL Server to another, two servers should be defined to each other as valid remote servers.
To set the configuration of a remote server:
· A group that extends a server.
· Right click on the server and click "Properties".
· Set options "Allow other SQL Servers to connect remotely to this SQL Server via RPC".
· Sets the value of the query time Out option, which specifies the number of seconds that can wait to return from a query processing. The default value is 0, which indicates that unlimited wait times are allowed.
· After setting the configuration options, click OK.
· After restarting the server, the modifications will take effect.
· Repeat the same steps on the other remote server.
Calling a remote stored procedure requires indicating the name of the server, followed by the name of the database and the owner's name. The following is an example of a stored procedure being raised on a different server (SERVER2).
Exec Server2.pubs.dbo.myproc

The something of peas:
This is a superficial introduction to SQL Server's common knowledge, and objects are programmers who write applications based on SQL Server databases, not database administrators. For application programmers, however, it is also useful to understand the management of the database. It is recommended that you understand the management of the database at a later time, which is also very useful for the optimizer.