Transact-SQL User-Defined Functions - Novick Software
Transact-SQL
User-Defined
Functions
Andrew Novick
Wordware Publishing, Inc.
Library of Congress Cataloging-in-Publication Data
Novick, Andrew N.
Transact-SQL user-defined functions / by Andrew Novick.
p. cm.
Includes bibliographical references and index.
ISBN 1-55622-079-0 (pbk.)
1. SQL server. 2. Database management. I. Title.
QA76.9.D3 N695 2003
005.75'85--dc22
2003020942
CIP
? 2004, Wordware Publishing, Inc.
All Rights Reserved
2320 Los Rios Boulevard
Plano, Texas 75074
No part of this book may be reproduced in any form or by any means
without permission in writing from Wordware Publishing, Inc.
Printed in the United States of America
ISBN 1-55622-079-0
10 9 8 7 6 5 4 3 2 1
0309
Crystal Reports is a registered trademark of Crystal Decisions, Inc. in the United States and/or other countries.
Names of Crystal Decisions products referenced herein are trademarks or registered trademarks of Crystal Decisions or
its
Transact-SQL is a trademark of Sybase, Inc. or its subsidiaries.
SQL Server is a trademark of Microsoft Corporation in the United States and/or other countries.
All brand names and product names mentioned in this book are trademarks or service marks of their respective companies.
Any omission or misuse (of any kind) of service marks or trademarks should not be regarded as intent to infringe on the
property of others. The publisher recognizes and respects all marks used by companies, manufacturers, and developers as
a means to distinguish their products.
This book is sold as is, without warranty of any kind, either express or implied, respecting the contents of this book and any
disks or programs that may accompany it, including but not limited to implied warranties for the book¡¯s quality,
performance, merchantability, or fitness for any particular purpose. Neither Wordware Publishing, Inc. nor its dealers or
distributors shall be liable to the purchaser or any other person or entity with respect to any liability, loss, or damage caused
or alleged to have been caused directly or indirectly by this book.
All inquiries for volume purchases of this book should be addressed to Wordware Publishing,
Inc., at the above address. Telephone inquiries may be made by calling:
(972) 423-0090
To my parents, Toni and Larry Novick
This page intentionally left blank.
Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Part I¡ªCreating and Using User-Defined Functions . . . . . 1
1 Overview of User-Defined Functions . . . . . . . . . . . . . 3
Introduction to UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Scalar UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Inline User-Defined Functions . . . . . . . . . . . . . . . . . . . . 9
Multistatement Table-Valued User-Defined Functions . . . . . . . 12
Why Use UDFs? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Reuse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Organizing Code through Modularization . . . . . . . . . . . . . . 19
Ease of Coding . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Why Not Use UDFs? . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2 Scalar UDFs . . . . . . . . . . . . . . . . . . . . . . . . . .
Creating, Dropping, and Altering Scalar UDFs. . . . . . . . . . . . .
Permissions to Use CREATE/DROP/ALTER FUNCTION . . . .
Using the CREATE FUNCTION Statement . . . . . . . . . . . .
The Function Body . . . . . . . . . . . . . . . . . . . . . . . . .
Declaring Local Variables (Including TABLEs) . . . . . . . . .
Control-of-flow Statements and Cursors . . . . . . . . . . . . .
Using SQL DML in Scalar UDFs. . . . . . . . . . . . . . . . .
Adding the WITH Clause . . . . . . . . . . . . . . . . . . . . . .
Specifying WITH ENCRYPTION . . . . . . . . . . . . . . . .
Specifying WITH SCHEMABINDING. . . . . . . . . . . . . .
Using Scalar UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . .
Granting Permission to Use Scalar UDFs. . . . . . . . . . . . . .
Using Scalar UDFs in SQL DML . . . . . . . . . . . . . . . . . .
Using Scalar UDFs in the Select List . . . . . . . . . . . . . .
Using Scalar UDFs in the WHERE and ORDER BY Clauses . .
Using Scalar UDFs in the ON Clause of a JOIN . . . . . . . . .
Using Scalar UDFs in INSERT, UPDATE, and DELETE
Statements . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Scalar UDFs in SET Statements . . . . . . . . . . . . .
Using Scalar UDFs in EXECUTE and PRINT Statements . . .
23
25
25
28
31
31
33
34
36
36
39
44
44
45
45
47
49
50
50
51
v
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- transact sql functions 135
- t sql data types tsql tutorial
- defining a table valued function in transact sql
- itzik ben gan solid quality mentors
- user defined financial functions for ms sql server
- handling missing values in the sql procedure
- t sql querying
- sql cheat sheet body
- transact sql user defined functions novick software