구글와이드(336x280)_상단 2개


(영문) Stored Procedures: An Overview DataBase(MS SQL)

Stored Procedures: An Overview

By Bill Graziano on 01 September 2000 | 0 Comments | Tags: Stored Procedures

This article covers the basic of writing a stored procedure. It's the first in a series on writing stored procedures.
A stored procedure is written using Transact-SQL (T-SQL). T-SQL is a subset of ANSI SQL-92 that has extensions to the Standard. T-SQL includes variables, conditional logic, loops and flow control. We'll use the pubs database for our examples. A stored procedure allows you to put code or business logic on the database server.

A stored procedure allows for more modular programming. You can create reusable, discrete pieces of functionality using stored procedures. Stored procedures have the parsing and execution plan built at the time they are created so they execute faster than dynamic SQL. If you have a stored procedure that manipulates large amounts of data, that data will remain on the server and not be transported across the network. You can also use stored procedures for security. You can grant users execute permissions on stored procedures without granting them permissions on objects manipulated by those stored procedures.

You can use SQL Server's Enterprise Manager to create and edit stored procedures. A simple stored procedure looks like:

CREATE PROCEDURE spCaliforniaAuthors
AS
SELECT * FROM authors
WHERE state = 'CA'
ORDER BY zip
This stored procedure is called "spCaliforniaAuthors". All it contains is a SELECT statement. All stored procedures that SQL Server provides start with "sp_" (and "xp_" for extended stored procedures) and I chose to almost follow this convention for this stored procedure. If you try to call a stored procedure that starts with "sp_" SQL Server will first search the MASTER database before searching the current database.

By default, only members of the dbo_owner role and db_ddladmin role can create stored procedures. Members of the dbo_owner role can give other users the ability to create procedures using a GRANT statement. That might look something like this:

GRANT CREATE PROCEDURE TO Development
You can check Books Online for more information on the GRANT statement.

You execute a stored procedure by typing it's name or using the EXECUTE statement. To execute our stored procedure you can type

EXECUTE spCaliforniaAuthors
This will execute the stored procedure and return the results. If you are calling this procedure from an ASP page (or other client) you can use the EXECUTE statement as you SQL string to execute. In this case, our stored procedure will return a record set.

That's enough for now. Over the next week or two I'll cover all aspects of writing stored procedures. Enjoy.

null



바보들의 영문법 카페(클릭!!)

오늘의 메모....

시사평론-정론직필 다음 카페
http://cafe.daum.net/sisa-1

바보들의 영문법 다음 카페
http://cafe.daum.net/babo-edu/

티스토리 내 블로그
http://earthly.tistory.com/

내 블로그에 있는 모든 글들과 자료에 대한 펌과 링크는 무제한 허용됩니다.
(단, 내 블로그에 덧글쓰기가 차단된 자들에게는 펌, 트랙백, 핑백 등이 일체 허용되지 않음.)

그리고 내 블로그 최근글 목록을 제목별로 보시려면....
바로 아래에 있는 이전글 목록의 최근달을 클릭하시면 됩니다.
그러면 제목을 보고 편하게 글을 골라 보실 수 있습니다.

그리고 내 블로그내 글을 검색하시려면 아래 검색버튼을 이용하시면 됩니다.


가가챗창

flag_Visitors

free counters