Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
agulino
Starting Member
6 Posts |
 Posted - 2002-05-07 : 14:53:39
|
| I am using SQL Server 7.0. I unserstand from BOL that you should not prefix sprocs with "sp_" because of how SQL Server accesses sprocs. Does that go for the "sp" prefix as well, or is that OK to use?Anthony Gulinoagulino@stonesix.comStone Six Solutions, Inc.www.stonesix.com |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-07 : 15:38:44
|
| The answer is 'YES'.The following should explain it in greater detail:http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8033Getting Information: System Stored Procedures If Microsoft discourages you from accessing the system tables directly, how can you get system information? Four mechanisms are available in SQL Server 7.0: system stored procedures and system functions, which also exist in previous releases, and ANSI schema views and object property functions, which are new in SQL Server 7.0.First, Microsoft has provided a large collection of system stored procedures with SQL Server. System procedures are available in all releases, but SQL Server 7.0 has the most. Some system stored procedures let you manually carry out most of the activities that the SQL Server Enterprise Manager offers. These procedures can set up replication, define tasks and alerts, define traces, etc. Most of these procedures either select from or modify one or more system tables. Some procedures, such as sp_help, retrieve basic system information; procedures such as sp_who and sp_lock help in troubleshooting system behavior.All these procedures start with the characters sp_, but the letters 'sp' don't stand for stored procedure or system procedure--they stand for special. Procedures with names starting with sp_ are special; they behave in ways that no other procedures can. (I recommend that you not use sp_ as a generic prefix for user-defined stored procedures because such a naming scheme might lead to confusion between regular procedures and special ones.)These sp_ procedures are special in two ways. To understand how they're special, be aware that all stored procedures are objects in a specific database. To execute a user-defined procedure in your current database, simply give its name. To execute a user-defined procedure in another database, you must prefix the procedure name with the database name. For example, if you're in the Northwind database and you want to execute a regular procedure in Pubs, you can use EXECUTE pubs.dbo.reptq1.All the supplied system procedures exist in the Master database. However, because of their sp_ prefix, you don't need to give the name of the database when you want to execute them. If you want to run the sp_help procedure, just use EXECUTE sp_help.The second special thing about the sp_ procedures has to do with the system tables. Although the procedures exist in the Master database, if you execute them without prefixing a database name, they'll reference system tables in the database they're called from. For example, the sp_help procedure returns a list of all objects in the sysobjects system table. But every database has a sysobjects table, so which sysobjects table does the procedure use? The sp_help procedure references the sysobjects table in whatever database you called the procedure from. If you called from Pubs, you'll get the list of objects from Pubs; if you're in Northwind, you'll get a list of Northwind's objects. In addition, if you prefix the call to these procedures with a database name, SQL Server responds as if you had called the procedure from the specified database. For example, if you use EXECUTE pubs.dbo.sp_help, you get the list of objects in Pubs, no matter what database you start out in.The prefix sp_ is special, but it's not reserved. If you're a systems administrator, you have permission to create procedures in the Master database, so you can create your own special procedures. If you create a procedure in the Master database and the name of the procedure starts with sp_, your procedure will have the same special properties as the system-supplied procedures. For instance, you won't have to prefix the name with 'master' when you execute this procedure, even though it's an object in the Master database. And, if the procedure references a system table, it will refer to the table in the database you executed the procedure from.The ability to refer to a table in the current database applies only to system tables. If you create a user table in the Master database, the situation changes. For example, suppose you created the following procedure in the Master database: CREATE PROCEDURE sp_list_data AS SELECT * FROM mytableRETURN |
 |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-07 : 15:39:32
|
| Sorry. YES it is OK to use. |
|
|
|
|
|
|
|
|
sp_ issue