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


(영문) SQL Stored Procedures DataBase(MS SQL)

SQL Server Architecture (SQL Server 2000)
SQL Stored Procedures
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.
Microsoft® SQL Server™ 2000 stored procedures return data in four ways:
  • Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).

  • Return codes, which are always an integer value.

  • A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.

  • A global cursor that can be referenced outside the stored procedure.
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server. The IF statement in this example shows embedding conditional logic in a procedure to keep from sending a result set to the application:
IF (@QuantityOrdered < (SELECT QuantityOnHand
                  FROM Inventory
                  WHERE PartID = @PartOrdered) )
   BEGIN
   -- SQL statements to update tables and process order.
   END
ELSE
   BEGIN
   -- SELECT statement to retrieve the IDs of alternate items
   -- to suggest as replacements to the customer.
   END

Applications do not need to transmit all of the SQL statements in the procedure: they have to transmit only an EXECUTE or CALL statement containing the name of the procedure and the values of the parameters.
Stored procedures can also shield users from needing to know the details of the tables in the database. If a set of stored procedures supports all of the business functions users need to perform, users never need to access the tables directly; they can just execute the stored procedures that model the business processes with which they are familiar.
An illustration of this use of stored procedures is the SQL Server system stored procedures used to insulate users from the system tables. SQL Server includes a set of system stored procedures whose names usually start with sp_. These system stored procedures support all of the administrative tasks required to run a SQL Server system. You can administer a SQL Server system using the Transact-SQL administration-related statements (such as CREATE TABLE) or the system stored procedures, and never need to directly update the system tables.
Stored Procedures and Execution Plans
In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure. Also, the fully compiled execution plan for the stored procedure was retained in the SQL Server procedure cache, meaning that subsequent executions of the stored procedure could use the precompiled execution plan.
SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements.
SQL Server 2000 and SQL Server version 7.0 offer new alternatives for processing SQL statements. For more information, see Query Processor Architecture.
Temporary Stored Procedures
SQL Server 2000 also supports temporary stored procedures that, like temporary tables, are dropped automatically when you disconnect. Temporary stored procedures are stored in tempdb and are useful when connected to earlier versions of SQL Server. Temporary stored procedures can be used when an application builds dynamic Transact-SQL statements that are executed several times. Rather than have the Transact-SQL statements recompiled each time, you can create a temporary stored procedure that is compiled on the first execution, and then execute the precompiled plan multiple times. Heavy use of temporary stored procedures, however, can lead to contention on the system tables in tempdb.
Two features of SQL Server 2000 and SQL Server 7.0 eliminate the need for using temporary stored procedures:
  • Execution plans from prior SQL statements can be reused. This is especially powerful when coupled with the use of the new sp_executesql system stored procedure.

  • Natively support for the prepare/execute model of OLE DB and ODBC without using any stored procedures.
For more information about alternatives to using temporary stored procedures, see Execution Plan Caching and Reuse.
Stored Procedure Example
This simple stored procedure example illustrates three ways stored procedures can return data:
  1. It first issues a SELECT statement that returns a result set summarizing the order activity for the stores in the sales table.

  2. It then issues a SELECT statement that fills an output parameter.

  3. Finally, it has a RETURN statement with a SELECT statement that returns an integer. Return codes are generally used to pass back error checking information. This procedure runs without errors, so it returns another value to illustrate how returned codes are filled.
USE Northwind
GO
DROP PROCEDURE OrderSummary
GO
CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS
-- SELECT to return a result set summarizing
-- employee sales.
SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
     JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID

-- SELECT to fill the output parameter with the
-- maximum quantity from Order Details.
SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]

-- Return the number of all items ordered.
RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO

-- Test the stored procedure.

-- DECLARE variables to hold the return code
-- and output parameter.
DECLARE @OrderSum INT
DECLARE @LargestOrder INT

-- Execute the procedure, which returns
-- the result set from the first SELECT.
EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder OUTPUT

-- Use the return code and output parameter.
PRINT 'The size of the largest single order was: ' +
                 CONVERT(CHAR(6), @LargestOrder)
PRINT 'The sum of the quantities ordered was: ' +
                 CONVERT(CHAR(6), @OrderSum)
GO

The output from running this sample is:
EmployeeID  SummSales                  
----------- -------------------------- 
1           202,143.71                 
2           177,749.26                 
3           213,051.30                 
4           250,187.45                 
5           75,567.75                  
6           78,198.10                  
7           141,295.99                 
8           133,301.03                 
9           82,964.00                  

The size of the largest single order was: 130 
The sum of the quantities ordered was: 51317

http://msdn2.microsoft.com/en-us/library/aa174792.aspx
============================================================
Creating and Maintaining Databases (SQL Server 2000)
Stored Procedures

When you create an application with Microsoft® SQL Server™ 2000, the Transact-SQL programming language is the primary programming interface between your applications and the SQL Server database. When you use Transact-SQL programs, two methods are available for storing and executing the programs. You can store the programs locally and create applications that send the commands to SQL Server and process the results, or you can store the programs as stored procedures in SQL Server and create applications that execute the stored procedures and process the results.
Stored procedures in SQL Server are similar to procedures in other programming languages in that they can:
  • Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

  • Contain programming statements that perform operations in the database, including calling other procedures.

  • Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
You can use the Transact-SQL EXECUTE statement to run a stored procedure. Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression.
The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:
  • They allow modular programming.
    You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.
  • They allow faster execution.
    If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
  • They can reduce network traffic.
    An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
  • They can be used as a security mechanism.
    Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.
A SQL Server stored procedure is created with the Transact-SQL CREATE PROCEDURE statement and can be modified with the ALTER PROCEDURE statement. The stored procedure definition contains two primary components: the specification of the procedure name and its parameters, and the body of the procedure, which contains Transact-SQL statements that perform the procedure's operations.
====================================================
OLE DB and SQL Server (SQL Server 2000)
Catalog Stored Procedures
To support reporting of schema data, Microsoft® SQL Server™ 2000 client interfaces rely on system stored procedures that extract data from a server's catalog. As client software evolves, the catalog stored procedures also evolve.
When a SQLOLEDB consumer connects to SQL Server version 6.5, SQLOLEDB returns an informational error message stating that the catalog stored procedures are out of date.
SQLOLEDB is compatible with earlier versions of SQL Server. However, not all schema rowsets are supported on earlier versions of SQL Server unless the catalog stored procedures are upgraded to the current release level.
To upgrade the catalog stored procedures, use an appropriate client utility to run the Transact-SQL Instcat.sql script that ships with the most recent version of SQLOLEDB. Instcat.sql requires system administrator privilege.
Depending on the version of the server, Instcat.sql execution can generate many error messages. All generated errors can be safely ignored if the final line of execution output indicates success.

null



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

오늘의 메모....

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

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

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

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

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

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


가가챗창

flag_Visitors

free counters