Routines

In SQL Server 2000, stored procedures and user-defined functions are considered routines. The ANSI SCHEMA VIEW called ROUTINES contains information about all your procedures and user-defined functions, including the text that makes up the routine's definition. You can inspect this view and get information about all the routines in a database by selecting from this ANSI SCHEMA VIEW. I ran the following in the master database:

 SELECT routine_type, routine_name FROM INFORMATION_SCHEMA.ROUTINES 

Here are the partial results:

 routine_type routine_name -------------------- ------------------------------- PROCEDURE sp_MSgetrowmetadata PROCEDURE sp_MSindexspace FUNCTION fn_sqlvarbasetostr FUNCTION fn_helpcollations PROCEDURE sp_describe_cursor_tables PROCEDURE sp_MSIfExistsRemoteLogin FUNCTION fn_trace_getinfo PROCEDURE sp_MSmakedynsnapshotvws FUNCTION fn_chariswhitespace FUNCTION fn_generateparameterpattern FUNCTION fn_skipparameterargument PROCEDURE sp_MSmakeexpandproc FUNCTION fn_removeparameterwithargument 

As you'll see in more detail later, stored procedures and functions are invoked in different ways. Simply put, stored procedures can be invoked anywhere a statement can be executed, while a function is invoked inside another statement. You can use scalar functions anywhere that your SQL command expects a single value, and you can use table-valued functions in the FROM clause to indicate the rowset to be processed.



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net