Using stored procedures in SQL Server

The whys and the hows of using stored procedures in Microsoft SQL Server.

When you are leery of having users directly access tables or any other objects in your files, SQL Server lets you

safeguard these objects by setting up stored procedures. While the system stored procedures are created automatically when you install SQL Server, and are used to interact with system tables and other administrative tasks. SQL Server lets you create user-defined stored procedures for any task that is comprised of multiple statements and conditions. The calling application can then execute the stored procedure instead of the whole set of statements individually.

You can create the stored procedures by using the CREATE PROCEDURE statement. SQL Server will then store them in the current database. Each name must be unique within the database as well as unique to the user who creates them. You can also create stored procedures in the master database. Names created in the master database with the sp_ prefix can be accessed by any other database, a very handy feature. SQL Server will look for it first in the current database. If it's not there, it look in the master database when it is called by other databases.

You can create and store procedures in the tempdb database. They will be automatically dropped by SQL Server unless you issue a DROP PROCEDURE statement. To create a temporary object use the # prefix for local and ## for global temporary stored procedures. These function the same way as other user-defined stored procedures except that they will be dropped when the connection that creates them is broken. A temporary stored procedure can be called from any database.

When a stored procedure is created, its properties are stored in the sysobjects system table. All of its definitions will be stored in the syscomments system table. Since a stored procedure is stored in the current database you will have to create a stored procedure in other databases by making them the current database. This can be done by using the USE statement.

After a user-defined stored procedure is created, you can view parameters and definitions using the sp_helptext system stored procedure. In addition, you can view its properties using sp_help.

The main advantage of creating stored procedures is that they can be used to keep users from using tables directly. You must assign execute permission to users on the stored procedures. They are then able to run the stored procedure without having direct permissions on the table or object that is referenced by the stored procedure. This keeps your tables and objects safe.

About the Author

Barrie Sosinsky (barries@killerapps.com) is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.

For More Information

  • What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, and DB2 gurus are waiting to answer your toughest questions.

This was first published in August 2001

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close