Q&A

Q1: Where are stored procedures stored?
A1: A stored procedure is a database object, so it is stored in the database in which it is created. There are references to a stored procedure in several system tables, including sysobjects (one line per procedure), syscolumns (one line per parameter), sysdepends (one line per referenced table, view, or procedure), and syscomments (one line per block of 255 characters of source SQL).
Q2: Is a stored procedure faster than a batch?
A2: Tomorrow, we'll look more closely at procedure performance issues. For now, I'll say, "Yes, most of the time."
Q3: How do I get a list of all stored procedures in the system?
A3: On a database-by-database basis, you can execute this statement to retrieve a list of stored procedures: 
select name
from sysobjects
where type = "P"

You can also use the object browser in the Query Analyzer.

Q4: Can I write a system-stored procedure?
A4: Yes. You will learn how tomorrow.
Q5: What does "group number" refer to?
A5: If you try to create a procedure whose name already exists, you get this error message: 
Msg 2729, Level 16, State 1
Object 'oops'group number 1
already exists in the database.
Choose another procedure name

Group numbering enables you to create sets of procedures with the same name, but different sequence numbers. For instance, to add a procedure oops to group 2, I would execute

create proc oops;2
as
…

This creates a maintenance nightmare because (1) you can't run any stored procedures against the procedure, and (2) you have to drop all the procedures belonging to a group at once.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset