SQL Server 笔记：Stored Procedure

create procedure usp_get_authorinfo
as
select au_lname,au_fname as firstname,phone,address
from authors
order by phone
=================================
create procedure usp_get_authorinfo
@state char(10)
as
select au_lname,au_fname as firstname,phone,address
from authors
where state=@state
order by phone
=================================
drop procedure usp_get_authorinfo
=================================
sp_helptext usp_get_authorinfo

Session 11
Creating and Using Stored Procedures
Session 12
Trigger Happy
Session 13
Introducing Cursors
Session 14
Understanding Indexes
Session 15
Rules, Defaults, and Constraints
Session 16
Understanding Transactions and Locks

=====================================
Once you have created the stored procedure you can execute it from the SQL
Query Analyzer window using the following syntax:

EXECUTE MyStoredProcedure

You can use a shorthand version: PROC instead of PROCEDURE and EXEC instead
of EXECUTE.
In order to see the results this procedure returns you need to elaborate a bit:
You must place a return value into a variable in the calling procedure.
MyStoredProcedure returns an integer value that is subsequently assigned to the
@result variable:

DECLARE @result int
EXECUTE @result = MyStoredProcedure
PRINT CAST(@result as varchar(5))

=====================================
The following is an example of a stored procedure that accepts an input parameter
of a string type and returns an integer:

CREATE PROCEDURE MyStoredProcedure @State AS
DECLARE @count_authors int
SELECT @count_authors = COUNT(*) FROM authors where
state=@State
RETURN @count_authors

To find the number of authors living in the state of California you use the following
syntax:

DECLARE @result int
EXEC @result = MyStoredProcedure(‘CA’)

The stored procedure can accept input parameters and return a value with output
parameters (note the difference between stored-procedure return values and
output parameters). Here is the syntax for a stored procedure returning parameters:

CREATE PROCEDURE MyStoredProcedure
@state varchar(2),@count_authors int OUTPUT AS
SELECT @count_authors=COUNT(*) FROM authors where state=@state
RETURN 0

The calling-statement syntax has changed—you need to send a variable into
the stored procedure for the output parameter, as shown here:

DECLARE @result int
EXECUTE MyStoredProcedure ‘CA’, @result OUTPUT

You still have to convert between data types in order to print the
output value; if a return parameter is varchar however, you can
print it without conversion.

CREATE PROCEDURE MyStoredProcedure
- - returns total count for authors in specific state
@state varchar(2),@count_authors int OUTPUT AS
SELECT @count_authors=COUNT(*) FROM authors where state =
@state
RETURN 0

=========================================
Error Handling

IF @@ERROR <> 0
BEGIN
PRINT ‘AN ERROR HAS OCCURRED’
END

Table 11-1
Components of SQL Server Errors Reported to Clients| Component Description
Error number The unique number assigned to this error
Error message Concise information about the possible cause of the error
Severity The seriousness of the error
State code Additional information that you can use to diagnose a problem
(the same error might have one or more causes, depending on
the state of the system)
Procedure name The name of the stored procedure that raised the error
Line number The location; the line number of the T-SQL statement that
generated the error

SELECT * FROM master.dbo.sysmessages WHERE error = 8134

Here is the result you will get:
error severity dlevel description msglangid
------- -------- ------ -------------------------- ---------
8134 16 0 Divide by zero error encountered 1033
(1 row(s) affected)

You also can define an error of your own based on some business-logic criterion
specific to your application. You can do it by placing the RAISERROR statement
anywhere in a stored procedure or batch. The general format is as follows (the first
three arguments are required; those in brackets are optional):
RAISERROR (custom error message OR error message ID, severity
level, state, [argument (...n)], WITH [options...])
Here is an example of a custom error message, severity 3, state 5:
RAISERROR (‘Guess what? An Error!!’, 3,5)
Use your best judgment in selecting severity levels for your custom messages.
Severity levels 0 to 19 are considered informational; they are used to report execution
status or return some trace message. For example, level 10 reports status, levels
11–16 are for errors that can be corrected by user, level 18 is for non-fatal
internal errors, and so on. Levels 20 to 25 indicate fatal errors from which the
application cannot recover: Level 23 indicates a database-integrity problem, and
level 24 indicates hardware failure. For a full list of severity levels please consult
Books Online.

====================================
D. 使用参数默认值 NULL 创建存储过程
参数默认值可以是 NULL 值。在这种情况下，如果未提供参数，则 SQL Server 将根据存储过程的其它语句执行存储过程。不会显示错误信息。

过程定义还可指定当不给出参数时要采取的其它某种措施。例如：

CREATE PROC showind3 @table varchar(30) = NULL
AS
IF @table IS NULL
PRINT 'Give a table name'
ELSE
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table

===============================================