SQLServerCh15
Gửi bởi: Khoa CNTT - HCEM 24 tháng 2 2021 lúc 9:38:27 | Được cập nhật: 25 tháng 3 lúc 13:27:00 Kiểu file: PPTX | Lượt xem: 214 | Lượt Download: 1 | File size: 1.548837 Mb
Nội dung tài liệu
Tải xuống
Link tài liệu:
Các tài liệu liên quan
Có thể bạn quan tâm
Thông tin tài liệu
Chapter 15
How to code
stored procedures,
functions, and triggers
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
Given the specifications for a database problem, write a stored
procedure that solves it. Include data validation when necessary.
Given a formula or expression, write a scalar-valued user-defined
function based on the formula or expression.
Given a SELECT statement with a WHERE clause, write a tablevalued user-defined function that replaces it.
Given the specifications for a database problem that could be
caused by an action query, write a trigger that prevents the
problem.
Given the specifications for a database problem that could be
caused by a DDL statement, write a trigger that prevents the
problem.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 2
Objectives (cont.)
Knowledge
Explain why a stored procedure executes faster than an equivalent
SQL script.
Describe the basic process for validating data within a stored
procedure.
Describe the basic purpose of the system stored procedures.
Describe the two types of user-defined functions.
Describe the two types of triggers.
Describe the effects of the WITH ENCRYPTION and WITH
SCHEMABINDING clauses on a stored procedure, user-defined
function, or trigger.
Explain why you’d want to use the ALTER statement rather than
dropping and recreating a procedure, function, or trigger.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 3
Objectives (cont.)
Given a stored procedure, user-defined function, or trigger,
explain what each statement does.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 4
A comparison of the different types
of procedural SQL programs
How it’s
Batches stored
Multiple In a file on
a disk
One only In an object in
the database
Accepts
parameters
No
User-defined
function
One only
Yes
Trigger
One only
Type
Script
Stored
procedure
Murach's SQL Server 2012, C15
How it’s
executed
Within a
client tool
By a program
or within a
SQL script
In an object in By a program
the database
or within a
SQL script
In an object in Automatically
the database
by the server
when an action
query is executed
© 2012, Mike Murach & Associates, Inc.
Yes
No
Slide 5
How stored procedures are used
By SQL programmers to control who accesses the database and
how
By application programmers to simplify their use of the database
How user-defined functions are used
Most often used by SQL programmers within the stored
procedures and triggers they write
Can also be used by application programmers and end users
How triggers are used
By SQL programmers to prevent database errors when an action
query is executed
By SQL programmers to provide for updatable views
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 6
A script that creates a stored procedure
USE AP;
GO
CREATE PROC spInvoiceReport
AS
SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE InvoiceTotal – CreditTotal – PaymentTotal > 0
ORDER BY VendorName;
The response from the system
Command(s) completed successfully.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 7
A statement that calls the procedure
EXEC spInvoiceReport;
The result set created by the procedure
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 8
The syntax of the CREATE PROC statement
CREATE {PROC|PROCEDURE} procedure_name
[parameter_declarations]
[WITH [RECOMPILE] [, ENCRYPTION] [, EXECUTE_AS_clause]]
AS sql_statements
A script that creates a stored procedure
that copies a table
USE AP;
IF OBJECT_ID('spCopyInvoices') IS NOT NULL
DROP PROC spCopyInvoices;
GO
CREATE PROC spCopyInvoices
AS
IF OBJECT_ID('InvoiceCopy') IS NOT NULL
DROP TABLE InvoiceCopy;
SELECT *
INTO InvoiceCopy
FROM Invoices;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 9
The syntax for declaring parameters
@parameter_name_1 data_type [= default] [OUTPUT]
[, @parameter_name_2 data_type [= default] [OUTPUT]]...
Typical parameter declarations
-- Input parameter that accepts a date/time value
@DateVar smalldatetime
-- Optional input parameter that accepts a character value
@VendorVar varchar(40) = NULL
-- Output parameter that returns a monetary value
@InvTotal money OUTPUT
Note
It’s a good programming practice to code CREATE PROC
statements so they list required parameters first, followed by
optional parameters.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 10
A procedure that uses an input
and an output parameter
CREATE PROC spInvTotal1
@DateVar smalldatetime,
@InvTotal money OUTPUT
AS
SELECT @InvTotal = SUM(InvoiceTotal)
FROM Invoices
WHERE InvoiceDate >= @DateVar;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 11
A procedure that uses an optional parameter
CREATE PROC spInvTotal2
@DateVar smalldatetime = NULL
AS
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
SELECT SUM(InvoiceTotal)
FROM Invoices
WHERE InvoiceDate >= @DateVar;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 12
A procedure that includes three parameters
CREATE PROC spInvTotal3
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
AS
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
SELECT @InvTotal = SUM(InvoiceTotal)
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE (InvoiceDate >= @DateVar) AND
(VendorName LIKE @VendorVar);
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 13
Code that passes the parameters by position
DECLARE @MyInvTotal money;
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2012-02-01', 'P%';
Code that passes the parameters by name
DECLARE @MyInvTotal money;
EXEC spInvTotal3 @DateVar = '2012-02-01',
@VendorVar = 'P%', @InvTotal = @MyInvTotal OUTPUT;
Code that omits one optional parameter
DECLARE @MyInvTotal money;
EXEC spInvTotal3 @VendorVar = 'M%',
@InvTotal = @MyInvTotal OUTPUT;
Code that omits both optional parameters
DECLARE @MyInvTotal money;
EXEC spInvTotal3 @MyInvTotal OUTPUT;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 14
The syntax of the RETURN statement
for a stored procedure
RETURN [integer_expression]
A stored procedure that returns a value
CREATE PROC spInvCount
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
AS
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
DECLARE @InvCount int;
SELECT @InvCount = COUNT(InvoiceID)
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE (InvoiceDate >= @DateVar) AND
(VendorName LIKE @VendorVar);
RETURN @InvCount;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 15
A script that calls the stored procedure
DECLARE @InvCount int;
EXEC @InvCount = spInvCount '2012-02-01', 'P%';
PRINT 'Invoice count: ' + CONVERT(varchar, @InvCount);
The response from the system
Invoice count: 6
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 16
The syntax of the THROW statement
THROW [error_number, message, state]
A stored procedure that tests
for a valid foreign key
CREATE PROC spInsertInvoice
@VendorID
int,
@InvoiceNumber varchar(50),
@InvoiceDate smalldatetime,
@InvoiceTotal
money,
@TermsID
int,
@InvoiceDueDate smalldatetime
AS
IF EXISTS(SELECT * FROM Vendors WHERE VendorID = @VendorID)
INSERT Invoices
VALUES (@VendorID, @InvoiceNumber,
@InvoiceDate, @InvoiceTotal, 0, 0,
@TermsID, @InvoiceDueDate, NULL);
ELSE
THROW 50001, 'Not a valid VendorID!', 1;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 17
A script that calls the procedure
BEGIN TRY
EXEC spInsertInvoice
799,'ZXK-799','2012-05-01',299.95,1,'2012-06-01';
END TRY
BEGIN CATCH
PRINT 'An error occurred.';
PRINT 'Message: ' + CONVERT(varchar, ERROR_MESSAGE());
IF ERROR_NUMBER() >= 50000
PRINT 'This is a custom error message.';
END CATCH;
The response from the system
An error occurred.
Message: Not a valid VendorID!
This is a custom error message.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 18
A THROW statement coded within a block
BEGIN
;
THROW 50001, 'Not a valid VendorID!', 1;
END;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 19
Terms
Temporary stored procedure
Local stored procedure
Input parameter
Output parameter
Optional parameter
Pass parameters by position
Pass parameters by name
Data validation
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 20
A stored procedure that validates the data
in a new invoice (part 1)
/*
Handles insertion of new invoices into AP database,
including data validation.
Author:
Bryan Syverson
Created:
2002-07-17
Modified:
2008-07-29 by Joel Murach
2012-05-09 by Anne Boehm
Return value:
InvoiceID for the new row if successful,
0 if unsuccessful
*/
USE AP;
GO
IF OBJECT_ID('spInsertInvoice') IS NOT NULL
DROP PROC spInsertInvoice;
GO
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 21
A stored procedure that validates the data
in a new invoice (part 2)
CREATE PROC spInsertInvoice
@VendorID
int = NULL,
@InvoiceNumber varchar(50) = NULL,
@InvoiceDate
smalldatetime = NULL,
@InvoiceTotal
money = NULL,
@PaymentTotal
money = NULL,
@CreditTotal
money = NULL,
@TermsID
int = NULL,
@InvoiceDueDate smalldatetime = NULL,
@PaymentDate
smalldatetime = NULL
AS
IF NOT EXISTS (SELECT * FROM Vendors
WHERE VendorID = @VendorID)
THROW 50001, 'Invalid VendorID.', 1;
IF @InvoiceNumber IS NULL
THROW 50001, 'Invalid InvoiceNumber.', 1;
IF @InvoiceDate IS NULL OR @InvoiceDate > GETDATE()
OR DATEDIFF(dd, @InvoiceDate, GETDATE()) > 30
THROW 50001, 'Invalid InvoiceDate.', 1;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 22
A stored procedure that validates the data
in a new invoice (part 3)
IF @InvoiceTotal IS NULL OR @InvoiceTotal <= 0
THROW 50001, 'Invalid InvoiceTotal.', 1;
IF @PaymentTotal IS NULL
SET @PaymentTotal = 0;
IF @CreditTotal IS NULL
SET @CreditTotal = 0;
IF @CreditTotal > @InvoiceTotal
THROW 50001, 'Invalid CreditTotal.', 1;
IF @PaymentTotal > @InvoiceTotal - @CreditTotal
THROW 50001, 'Invalid PaymentTotal.', 1;
IF NOT EXISTS (SELECT * FROM Terms
WHERE TermsID = @TermsID)
IF @TermsID IS NULL
SELECT @TermsID = DefaultTermsID
FROM Vendors
WHERE VendorID = @VendorID;
ELSE -- @TermsID IS NOT NULL
THROW 50001, 'Invalid TermsID.', 1;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 23
A stored procedure that validates the data
in a new invoice (part 4)
IF @InvoiceDueDate IS NULL
SET @InvoiceDueDate = @InvoiceDate +
(SELECT TermsDueDays FROM Terms
WHERE TermsID = @TermsID);
ELSE -- @InvoiceDueDate IS NOT NULL
IF @InvoiceDueDate < @InvoiceDate OR
DATEDIFF(dd, @InvoiceDueDate, @InvoiceDate)
> 180
THROW 50001, 'Invalid InvoiceDueDate.', 1;
IF @PaymentDate < @InvoiceDate OR
DATEDIFF(dd, @PaymentDate, GETDATE()) > 14
THROW 50001, 'Invalid PaymentDate.', 1;
INSERT Invoices
VALUES (@VendorID, @InvoiceNumber, @InvoiceDate,
@InvoiceTotal, @PaymentTotal, @CreditTotal,
@TermsID, @InvoiceDueDate, @PaymentDate);
RETURN @@IDENTITY;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 24
A SQL script that calls the stored procedure
BEGIN TRY
DECLARE @InvoiceID int;
EXEC @InvoiceID = spInsertInvoice
@VendorID = 799,
@InvoiceNumber = 'RZ99381',
@InvoiceDate = '2012-04-12',
@InvoiceTotal = 1292.45;
PRINT 'Row was inserted.';
PRINT 'New InvoiceID: ' + CONVERT(varchar, @InvoiceID);
END TRY
BEGIN CATCH
PRINT 'An error occurred. Row was not inserted.';
PRINT 'Error number: ' +
CONVERT(varchar, ERROR_NUMBER());
PRINT 'Error message: ' +
CONVERT(varchar, ERROR_MESSAGE());
END CATCH;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 25
The response for a successful insert
Row was inserted.
New InvoiceID: 115
The response when a validation error occurs
An error occurred. Row was not inserted.
Error number: 50001
Error message: Invalid VendorID.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 26
The syntax for creating a user-defined table type
CREATE TYPE TableTypeName AS
TABLE
table_definition
A statement that creates a user-defined table type
CREATE TYPE LineItems AS
TABLE
(InvoiceID
INT
NOT NULL,
InvoiceSequence
SMALLINT
NOT NULL,
AccountNo
INT
NOT NULL,
ItemAmount
MONEY
NOT NULL,
ItemDescription
VARCHAR(100) NOT NULL,
PRIMARY KEY (InvoiceID, InvoiceSequence));
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 27
A stored procedure that accepts a table
as a parameter
CREATE PROC spInsertLineItems
@LineItems LineItems READONLY
AS
INSERT INTO InvoiceLineItems
SELECT *
FROM @LineItems;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 28
Statements that pass a table
to the stored procedure
DECLARE @LineItems LineItems;
INSERT INTO @LineItems
VALUES (114, 1, 553, 127.75, 'Freight');
INSERT INTO @LineItems
VALUES (114, 2, 553, 29.25, 'Freight');
INSERT INTO @LineItems
VALUES (114, 3, 553, 48.50, 'Freight');
EXEC spInsertLineItems @LineItems;
The response from the system
(1
(1
(1
(3
row(s)
row(s)
row(s)
row(s)
Murach's SQL Server 2012, C15
affected)
affected)
affected)
affected)
© 2012, Mike Murach & Associates, Inc.
Slide 29
The syntax of the DROP PROC statement
DROP {PROC|PROCEDURE} procedure_name [, ...]
The syntax of the ALTER PROC statement
ALTER {PROC|PROCEDURE} procedure_name
[parameter declarations]
[WITH [RECOMPILE] [, ENCRYPTION] [, EXECUTE_AS_clause]]
AS sql_statements
Note
When you delete a procedure, any security permissions that are
assigned to the procedure are also deleted.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 30
A statement that creates a procedure
CREATE PROC spVendorState
@State varchar(20)
AS
SELECT VendorName
FROM Vendors
WHERE VendorState = @State;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 31
Change the parameter defined by the procedure
ALTER PROC spVendorState
@State varchar(20) = NULL
AS
IF @State IS NULL
SELECT VendorName
FROM Vendors;
ELSE
SELECT VendorName
FROM Vendors
WHERE VendorState = @State;
Delete the procedure
DROP PROC spVendorState;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 32
Commonly used system stored procedures
sp_Help [name]
sp_HelpText name
sp_HelpDb [database_name]
sp_Who [login_ID]
sp_Columns name
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 33
How to use sp_HelpText
USE AP;
EXEC sp_HelpText spInvoiceReport;
The results returned by the procedure
The results if WITH ENCRYPTION is used
The text for object 'spInvoiceReport' is encrypted.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 34
The three types of user-defined functions
Function type
Scalar-valued
function
Simple table-valued
function
Multi-statement
table-valued function
Murach's SQL Server 2012, C15
Description
Returns a single value of any T-SQL data
type.
Returns a table that’s based on a single
SELECT statement.
Returns a table that’s based on multiple
statements.
© 2012, Mike Murach & Associates, Inc.
Slide 35
A statement that creates a scalar-valued function
CREATE FUNCTION fnVendorID
(@VendorName varchar(50))
RETURNS int
BEGIN
RETURN (SELECT VendorID FROM Vendors
WHERE VendorName = @VendorName);
END;
A statement that invokes the scalar-valued
function
SELECT InvoiceDate, InvoiceTotal
FROM Invoices
WHERE VendorID = dbo.fnVendorID('IBM');
A statement that invokes a table-valued function
SELECT * FROM dbo.fnTopVendorsDue(5000);
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 36
The syntax for creating a scalar-valued function
CREATE FUNCTION [schema_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS data_type
[WITH [ENCRYPTION] [, SCHEMABINDING] [,
EXECUTE_AS_clause]]
[AS]
BEGIN
[sql_statements]
RETURN scalar_expression
END
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 37
A statement that creates a scalar-valued function
CREATE FUNCTION fnBalanceDue()
RETURNS money
BEGIN
RETURN (SELECT SUM(InvoiceTotal – PaymentTotal –
CreditTotal)
FROM Invoices
WHERE InvoiceTotal – PaymentTotal –
CreditTotal > 0);
END;
A script that invokes the function
PRINT 'Balance due: $' +
CONVERT(varchar, dbo.fnBalanceDue(), 1);
The response from the system
Balance due: $32,020.42
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 38
The syntax for creating a simple
table-valued function
CREATE FUNCTION [schema_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS TABLE
[WITH [ENCRYPTION] [, SCHEMABINDING]]
[AS]
RETURN [(] select_statement [)]
A statement that creates a simple
table-valued function
CREATE FUNCTION fnTopVendorsDue
(@CutOff money = 0)
RETURNS table
RETURN
(SELECT VendorName, SUM(InvoiceTotal) AS TotalDue
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0
GROUP BY VendorName
HAVING SUM(InvoiceTotal) >= @CutOff);
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 39
A SELECT statement that invokes the function
SELECT * FROM dbo.fnTopVendorsDue(5000);
The result set
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 40
Use the function in a join operation
SELECT Vendors.VendorName, VendorCity, TotalDue
FROM Vendors JOIN dbo.fnTopVendorsDue(DEFAULT)
AS TopVendors
ON Vendors.VendorName = TopVendors.VendorName;
The result set
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 41
The syntax for creating a multi-statement
table-valued function
CREATE FUNCTION [schema_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS @return_variable TABLE
(column_name_1 data_type [column_attributes]
[, column_name_2 data_type [column_attributes]]...)
[WITH [ENCRYPTION] [, SCHEMABINDING]
[, EXECUTE_AS_clause]]
[AS]
BEGIN
sql_statements
RETURN
END
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 42
Create a multi-statement table-valued function
CREATE FUNCTION fnCreditAdj (@HowMuch money)
RETURNS @OutTable table
(InvoiceID int, VendorID int, InvoiceNumber varchar(50),
InvoiceDate smalldatetime, InvoiceTotal money,
PaymentTotal money, CreditTotal money)
BEGIN
INSERT @OutTable
SELECT InvoiceID, VendorID, InvoiceNumber, InvoiceDate,
InvoiceTotal, PaymentTotal, CreditTotal
FROM Invoices
WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0;
WHILE (SELECT SUM(InvoiceTotal - CreditTotal - PaymentTotal)
FROM @OutTable) >= @HowMuch
UPDATE @OutTable
SET CreditTotal = CreditTotal + .01
WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0;
RETURN;
END;
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 43
A SELECT statement that uses the function
SELECT VendorName, SUM(CreditTotal) AS CreditRequest
FROM Vendors JOIN dbo.fnCreditAdj(25000) AS CreditTable
ON Vendors.VendorID = CreditTable.VendorID
GROUP BY VendorName;
The response from the system
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 44
The syntax of the DROP FUNCTION statement
DROP FUNCTION [schema_name.]function_name [, ...]
Note
When you delete a function, any security permissions that are
assigned to the function and any dependencies between the
function and the tables and views it uses are also deleted.
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 45
The syntax of the ALTER FUNCTION statement
for a scalar-valued function
ALTER FUNCTION [schema_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS data_type
[WITH [ENCRYPTION] [, SCHEMABINDING] [,
EXECUTE_AS_clause]]
BEGIN
[sql_statements]
RETURN scalar_expression
END
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 46
The syntax for altering a simple
table-valued function
ALTER FUNCTION [schema_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS TABLE
[WITH [ENCRYPTION] [, SCHEMABINDING]]
RETURN [(] select_statement [)]
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 47
The syntax for altering a multi-statement
table-valued function
ALTER FUNCTION [schema_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS @return_variable TABLE
(column_name_1 data_type [column_attributes]
[, column_name_2 data_type [column_attributes]]...)
[WITH [ENCRYPTION] [, SCHEMABINDING]
[, EXECUTE_AS_clause]]
BEGIN
sql_statements
RETURN
END
Murach's SQL Server 2012, C15
© 2012, Mike Murach & Associates, Inc.
Slide 48