Transcript T-SQL Stored Procedures

SQL Server 2005
Implementation and
Maintenance
Chapter 5: Introducing More
Database Objects
Introducing Stored
Procedures
• A stored procedure is a predefined batch of code
• There are two main types
– T-SQL
– CLR (we will not discuss here)
• SQL Server has many built-in
stored procedures, called System
Stored Procedures
© Wiley Inc. 2006. All Rights Reserved.
System Stored
Procedures
• Used for maintenance and
management activities
• They start with the sp_ prefix
– i.e. sp_tables
• Owned by the sys schema in each
database
© Wiley Inc. 2006. All Rights Reserved.
T-SQL Stored
Procedures
• These are batches of T-SQL code
stored as an object in the
database
• They can accept input parameters
• They can return various output
types
© Wiley Inc. 2006. All Rights Reserved.
T-SQL Stored
Procedures Benefits
• Reduced network traffic
– Code is stored centrally on the
server, instead of being transmitted
over the network
• Code Reusability
– SQL Server compiles and stores
these in memory for future use
© Wiley Inc. 2006. All Rights Reserved.
T-SQL Stored
Procedures Benefits
• Enhanced Security
– Requires valid permissions to
execute
– Users can have permission to
execute a stored procedure, without
having permission to read from the
underlying tables or views
– You can specify the security context
• Owner, Caller or User
© Wiley Inc. 2006. All Rights Reserved.
CLR Stored Procedures
• Procedures written in a .NET CLR
compliant language
– E.g. Visual C#, Visual Basic.NET
• The compiled assembly is
cataloged as a stored procedure
• Visual Studio is the most popular
tool for writing these.
– There are more tools, even Notepad
will work
© Wiley Inc. 2006. All Rights Reserved.
Creating T-SQL Stored
Procedures
• Use the CREATE PROCEDURE
statement
• You can write it from scratch or
use a pre-defined template
© Wiley Inc. 2006. All Rights Reserved.
Creating CLR Stored
Procedures
• Enable CLR support in SQL
Server
– sp_configure ‘clr_enabled’, 1
• Write the procedure in a CLR
compliant language
• Compile the procedure into a DLL
• Create the assembly using the
CREATE ASSEMBLY statement
© Wiley Inc. 2006. All Rights Reserved.
Recompiling Stored
Procedures
• SQL Server creates an execution plan
the first time it executes a procedure
– The plan is then stored in cache
• Stored procedures are recompiled
automatically if the underlying table or
view is changed
• You may need to manually recompile if
– You add a new index
– Extensive updates occur on the table
© Wiley Inc. 2006. All Rights Reserved.
SP with Transaction Sample
CREATE PROCEDURE SP_ChangeSupplier @FromSID
INT,@ToSID INT, @Result INT OUTPUT AS
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
Select @ErrorSave = 0
-- Begin Transaction
Begin Transaction
Update Products
Set SupplierID = @ToSID
WHERE SupplierID =@FromSID
-- Check if the update is successful
IF (@@ERROR <> 0)
Begin
Select @ErrorSave = -1
Rollback Transaction -- rollback
end
else
commit Transaction -- commit
Select @result = @ErrorSave
GO
Calling the SP
declare @r int
select @r = 100
exec SP_ChangeSupplier 30, 37, @r
output
print '>>' + ltrim(str(@r)) + '<<'
Introducing Triggers
• Triggers are very similar to stored
procedures
• One big difference is: triggers
cannot be manually executed
– They only execute in response to a
user action, like an INSERT
• There are two types of triggers
– Data Manipulation Language (DML)
– Data Definition Language (DDL)
© Wiley Inc. 2006. All Rights Reserved.
DML Triggers
• These execute automatically
when a DML statement is run
– INSERT, UPDATE, or DELETE
• INSERT triggers Use the
INSERTED table
• DELETE triggers use the
DELETED table
• UPDATE triggers use both tables
© Wiley Inc. 2006. All Rights Reserved.
INSTEAD OF Triggers
• By default, all triggers are AFTER
triggers
– The trigger fires after the statement
is executed
• INSTEAD OF triggers fire before
the statement is executed
• These can be used to modify a
user query before it is applied to
the table or view
© Wiley Inc. 2006. All Rights Reserved.
DDL Triggers
• These execute when a DDL
statement is run
– I.e. ALTER TABLE
• These can tell you when a
database object has been
modified
• They can even prevent
modifications
© Wiley Inc. 2006. All Rights Reserved.
Introducing Functions
• These are also similar to stored
procedures
• Functions can be called within a
SELECT statement or WHERE clause
– Stored Procedures can only be called with
an EXEC statement
• There are four main types
–
–
–
–
Scalar
Table-valued
Built-in
CLR
© Wiley Inc. 2006. All Rights Reserved.
Function Types
• Scalar functions
– These return a single value
• Table-valued functions
– These return a table result set
• Built-in functions
– These perform a variety of functions
• CLR functions
– Custom functions written in a CLR
compliant language (i.e. Visual C#)
© Wiley Inc. 2006. All Rights Reserved.
Introducing UserDefined Datatypes
• SQL Server has many built-in
datatypes
– Varchar, int, datetime, etc.
• They do not always fit your needs
• You can create your own
datatypes using T-SQL or CLR
code
© Wiley Inc. 2006. All Rights Reserved.