Stored Procedures and User-Defined Functions

Stored Procedures and User -Defined Functions

Stored Procedures

In any database system, you will be required to develop custom procedures and functions. Use of stored procedures and the new functionality of user “defined functions are covered in Chapter 9. A stored procedure can have input parameters, which make it possible to tell the stored procedure things like the parameters for the queries it should run. A stored procedure can return results in several ways: with an integer return code, with one or more resultsets, and with output parameters.

To pass back a number to the calling process, use the RETURN statement. The RETURN statement immediately exits the stored procedure, so don't use it until the stored procedure is done. Stored procedures can also pass data to the calling application by using output parameters. Output parameters are declared like other parameters in the CREATE PROCEDURE statement with the word OUTPUT after them.

Cursor Status

If you're in doubt about the validity of the cursor variable, you can use the CURSOR_STATUS() function. This function returns different values depending on the condition of the cursor ”provided you know the type of cursor (local, global, or variable). The syntax is CURSOR_STATUS('<type>', '<variable>') . The single ticks are mandatory. For example, to diagnose the preceding cursor, you could use CURSOR_STATUS ('variable', '@OutputCursor') . Table 7 shows what the CURSOR_STATUS function returns.

Table 7. CURSOR_STATUS Return Values

Return Value	Explanation
1	The cursor is allocated, open , valid, and has at least one row if it is a static cursor, or may have zero rows if it is dynamic.
	The cursor is allocated and open, but it is empty. This can happen only on static cursors .
1	The cursor is closed.
2	An error occurred because the variable wasn't assigned or the cursor was closed and deallocated.
3	The cursor doesn't exist.

Error Handling

If @@ERROR is set to something besides zero, then an error occurred while attempting to run the statement. The @@ERROR variable can be used to handle errors that won't stop a batch (or a stored procedure) from running, such as constraint violations or problems with aggregate functions that don't have enough data.

User-Defined Functions

User-defined functions have been on the most- requested -feature lists of most SQL Server programmers for a very long time. We use the system-defined functions, such as USER_NAME() , @@ SERVERNAME , CONVERT() , and GETDATE() constantly ”they provide the functionality that makes T-SQL useful in many respects. By passing back basic values, user-defined functions can be used in many places where stored procedures just wouldn't work.

The following list shows system-defined functions not allowed inside a user-defined function:

@@CONNECTIONS	@@PACK_SENT	GETDATE()
@@CPU_BUSY	@@PACKET_ERRORS	GETUTCDATE()
@@IDLE	@@TIMETICKS	NEWID()
@@IO_BUSY	@@TOTAL_ERRORS	RAND()
@@MAX_CONNECTIONS	@@TOTAL_READ	TEXTPTR
@@PACK_RECEIVED	@@TOTAL_WRITE