Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

Creating a Stored Procedure

As I've said, you use the Transact-SQL CREATE PROCEDURE command to create stored procedures. All that really happens when you create a procedure is that its syntax is checked and its source code is inserted into the syscomments system table. Generally, object names referenced by a procedure aren't resolved until it's executed. In SQL Server parlance, this is known as deferred name resolution.

"Syscomments" is a misnomer. The table doesn't store comments per se; it stores source code. The name is a vestige from the pre-7.0 days and was so named because it stored the optional source code to stored procedures (and other objects), whereas sysprocedures stored a pseudo-compiled version (a kind of normalized query tree) of the procedures themselves. This is no longer true, and the sysprocedures table no longer exists. Syscomments is now the sole repository for stored procedures, views, triggers, user-defined functions (UDFs), rules, and defaults. If you remove an object's source code from syscomments, you can no longer use that object.

Deferred Name Resolution and an Interesting Exception

Before we go further, it's worth mentioning that there's an interesting exception to SQL Server's default deferred name resolution behavior. Run the code in Listing 1–2 in Query Analyzer:

Listing 1–2 SQL Server doesn't allow you to include more than one CREATE TABLE statement for a given temporary table in the same stored procedure.

CREATE PROC testp @var int
AS
IF @var=1
 CREATE TABLE #temp (k1 int identity, c1 int)
ELSE
 CREATE TABLE #temp (k1 int identity, c1 varchar(2))
INSERT #temp DEFAULT VALUES
SELECT c1 FROM #temp
GO

The syntax contained in the stored procedure is seemingly valid, yet we get this message when we run it:

Server: Msg 2714, Level 16, State 1, Procedure testp, Line 6
There is already an object named '#temp' in the database.

Why? Obviously @var can't be both equal to one and not equal to one, right? To get a little closer to the answer, change the temporary table reference to a permanent table, like the one shown in Listing 1–3.

Listing 1–3 Changing the table to a permanent table works around the temporary table limitation.

CREATE PROC testp @var int
AS
IF @var=1
 CREATE TABLE tempdb..temp (k1 int identity, c1 int)
ELSE
 CREATE TABLE tempdb..temp (k1 int identity, c1 varchar(2))
INSERT #temp DEFAULT VALUES
SELECT c1 FROM #temp
GO

This procedure is created without error. What's happening here? Why does SQL Server care whether the table created is a temporary or a permanent table? And why does it matter now—before the procedure is even executed and before the value of @var can be known?

What appears to be happening is that SQL Server resolves CREATE TABLE references to temporary tables before inserting the procedure into syscomments—an apparent vestige from the pre-7.0 days when object references were resolved when a procedure was first created. The same limitation applies to variable declarations and, therefore, to the table data type. You can't declare a variable more than once in a single stored procedure, even if the declarations reside in mutually exclusive units of code. This differs from how permanent tables are handled, and is the reason the code in Listing 1–3 runs without error. It appears that, beginning with SQL Server 7.0, deferred name resolution was enabled for permanent tables, but not for temporary ones. Whatever the case, you can't execute code like that shown in Listing 1–2, so here's a workaround (Listing 1–4):

Listing 1–4 Including one CREATE TABLE statement, but two sets of ALTER TABLE statements, works around the problem.

CREATE PROC testp @var int
AS
CREATE TABLE #temp (k1 int identity)
IF @var=1
 ALTER TABLE #temp ADD c1 int
ELSE
 ALTER TABLE #temp ADD c1 varchar(2)
INSERT #temp DEFAULT VALUES
EXEC('SELECT c1 FROM #temp')
GO

This technique creates the table just once, then alters it to match the Data Definition Language (DDL) specification (spec) we want to end up with. Note the use of EXEC() to select the column we added with ALTER TABLE. The use of dynamic SQL is necessary because the newly added column isn't immediately visible to the procedure that added it. We're forced to create and execute an ad hoc query string to access it by name. (Note that you can reference the column indirectly—for example, through SELECT * or via an ordinal value in an ORDER BY clause, just not by name).

Another disadvantage of this approach is that it mixes DDL (the CREATE and ALTER statements) and Data Modification Language (DML; the INSERT and SELECT). Because of this, the procedure's execution plan must be recompiled when the INSERT is encountered (the temporary table's schema information (info) has changed since the original execution plan was formulated). Any stored procedure that creates a temporary table, then processes it further, will cause a plan recompile because the table's schema info did not exist when the execution plan was first created; however, the procedure in Listing 1–4 causes an additional recompile to occur because it alters this schema, then processes the table further. Particularly with large procedures in high-throughput environments, this can cause performance problems as well as blocking and concurrency issues because a compile lock is taken out on the stored procedure while the execution plan is being recompiled. Listing 1–5 presents a workaround that doesn't require the use of dynamic T-SQL:

Listing 1–5 A workaround for the temporary table creation problem.

CREATE PROCEDURE testp4
AS
INSERT #temp DEFAULT VALUES
SELECT c1 FROM #temp
GO
CREATE PROC testp3
AS
CREATE TABLE #temp (k1 int identity, c1 varchar(2))
EXEC dbo.testp4
GO
CREATE PROC testp2
AS
CREATE TABLE #temp (k1 int identity, c1 int)
EXEC dbo.testp4
GO
CREATE PROC testp @var int
AS
IF @var=1
EXEC dbo.testp2
ELSE
EXEC dbo.testp3
GO

Although this technique alleviates the need for EXEC(), it also forces us to completely reorganize the stored procedure. In fact, we're forced to break the original procedure into four separate routines and call the fourth one redundantly from the second and third routines. Why? First, instead of having two CREATE TABLE statements for the same temporary table in one procedure—which, as we've discovered, isn't supported—we moved each CREATE TABLE to its own procedure. Second, because a temporary table is automatically dropped as soon as it goes out of scope, we can't simply create it, then return to the top-level routine and add rows to it or SELECT from it. We have to do that either in one of the procedures that created it or in a common routine that they call. We chose the latter, so procedures two and three call a fourth routine that takes care of inserting the row into the temporary table and selecting the c1 column from it. (Because objects created in a procedure are visible to the procedures it calls, the fourth routine can "see" the table created by its caller.) This approach works, but is far from optimal. Think about how complex this would get for a really large procedure. Breaking it into multiple, distinct pieces may not be practical. Still, it avoids the necessity of having to create and execute an ad hoc T-SQL string and should generally perform better than that approach.

Listing a Stored Procedure

Assuming the object is not encrypted, you can list the source code to a procedure, view, trigger, UDF, rule, or default object using the sp_helptext system procedure. An example is included in Listing 1–6:

Listing 1–6 sp_helptext lists the source for a stored procedure.

EXEC dbo.sp_helptext 'ListCustomersByCity'
Text
---------------------------------------------------------------------------
CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'
AS
SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE @Country
GROUP BY City

Permissions and Limitations

Only members of the sysadmin, db_owner, or db_ddladmin role (or those explicitly granted CREATE PROC permission by a member of the appropriate role) can execute CREATE PROCEDURE.

The maximum stored procedure size is 128MB. The maximum number of parameters a procedure may receive is 1,024.

Creation Tips

Include a comment header with each procedure that identifies its author, purpose, creation date and revision history, the parameters it receives, and so forth. A common technique is to place this comment block either immediately before or just after the CREATE PROC statement itself (but before the rest of the procedure) to ensure that it's stored in syscomments and can be viewed from tools like Enterprise Manager and Query Analyzer's Object Browser. The system stored procedure that follows, sp_object_script_comments, generates comment headers for stored procedures, views, and similar objects (Listing 1–7):

Listing 1–7 You can use sp_object_script_comments to generate stored procedure comment headers.

USE master
GO
IF OBJECT_ID('dbo.sp_object_script_comments') IS NOT NULL
 DROP PROC dbo.sp_object_script_comments
GO
CREATE PROCEDURE dbo.sp_object_script_comments
          -- Required parameters
          @objectname sysname=NULL,
          @desc sysname=NULL,
          -- Optional parameters
          @parameters varchar(8000)=NULL,
          @example varchar(8000)=NULL,
          @author sysname=NULL,
          @workfile sysname='', -- Force workfile to be generated
          @email sysname='(none)',
          @version sysname=NULL,
          @revision sysname='0',
          @datecreated smalldatetime=NULL,
          @datelastchanged smalldatetime=NULL
/*
Object: sp_object_script_comments
Description: Generates comment headers for SQL scripts
Usage: sp_object_script_comments @objectname='ObjectName', @desc='Description of 
object",@parameters='param1[,param2...]'
Returns: (None)
$Workfile: sp_object_script_comments.sql $
$Author: Khen $. Email: khen@khen.com
$Revision: 1 $
Example: sp_object_script_comments @objectname='sp_who', @desc='Returns a list of
 currently running jobs', @parameters=[@loginname]
Created: 1992-04-03. $Modtime: 1/4/01 8:35p $.
*/
AS
IF (@objectname+@desc) IS NULL GOTO Help
PRINT '/*'
PRINT CHAR(13)
EXEC sp_usage @objectname=@objectname,
       @desc=@desc,
       @parameters=@parameters,
       @example=@example,
       @author=@author,
       @workfile=@workfile,
       @email=@email,
       @version=@version, @revision=@revision,
       @datecreated=@datecreated, @datelastchanged=@datelastchanged
PRINT CHAR(13)+'*/'
RETURN 0
Help:
EXEC dbo.sp_usage @objectname='sp_object_script_comments',
    @desc='Generates comment headers for SQL scripts',
    @parameters='@objectname=''ObjectName'',
    @desc=''Description of object",@parameters=''param1[,param2...]''',
    @example='sp_object_script_comments @objectname=''sp_who'',
    @desc=''Returns a list of currently running jobs'',
    @parameters=[@loginname]',
    @author='Ken Henderson',
    @workfile='sp_object_script_comments.sql',
    @email='khen@khen.com',
    @version='3', @revision='1',
    @datecreated='19920403', @datelastchanged='19990701'
RETURN -1
GO
EXEC dbo.sp_object_script_comments

This procedure generates stored procedure comment headers by calling the sp_usage procedure included later in the chapter. It can be executed from any database by any procedure. To use sp_object_script_comments, simply pass it the required parameters, and it will create a fully usable comment block that identifies a procedure or other type of object and spells out its usage and key background info. You can copy this block of text and paste it into the header of the routine itself and—voila!—you've got a nicely formatted, informative comment block for your code.

In shops with lots of stored procedure code, it's common to locate each stored procedure in its own script and to store each script in a version control or source code management system. Many of these systems support special tags (these are known as keywords in Visual SourceSafe [VSS], the source code management system that I use) that you can embed in T-SQL comments. Through these tags, you allow the source code management system to automatically insert revision information, the name of the person who last changed the file, the date and time of the last change, and so on. Because the tags are embedded in comments, there's no danger that these changes will break your code. Basically, you're just allowing the system to take care of some of the housekeeping normally associated with managing source code. Many of the stored procedures listed in this book include tags recognized by VSS in their headers (these tags begin and end with $). See Chapter 4 for more information.

Allow the passing of a single help parameter such as '/?'—or no parameters—to return an informational message telling the caller how to use the procedure. Place the section that generates this usage information at the end of the procedure to keep it out of the way and to locate it consistently from procedure to procedure. An ideal way to do this is to set up and call a separate procedure that accepts parameters indicating usage information and returns it in a uniform format. Here's a stored procedure that does just that (Listing 1–8):

Listing 1–8 You can use sp_usage to generate stored procedure usage info.

USE master
GO
IF OBJECT_ID('dbo.sp_usage') IS NOT NULL
 DROP PROC dbo.sp_usage
GO
CREATE PROCEDURE dbo.sp_usage
           -- Required parameters
           @objectname sysname=NULL,
           @desc sysname=NULL,
           -- Optional parameters
           @parameters varchar(8000)=NULL,
           @returns varchar(8000)='(None)',
           @example varchar(8000)=NULL,
           @workfile sysname=NULL,
           @author sysname=NULL,
           @email sysname='(none)',
           @version sysname=NULL,
           @revision sysname='0',
           @datecreated smalldatetime=NULL,
           @datelastchanged smalldatetime=NULL
/*
Object: sp_usage
Description: Provides usage information for stored procedures and descriptions of 
other types of objects
Usage: sp_usage @objectname='ObjectName', @desc='Description of object'
       [, @parameters='param1,param2...']
       [, @example='Example of usage']
       [, @workfile='File name of script']
       [, @author='Object author']
       [, @email='Author email']
       [, @version='Version number or info']
       [, @revision='Revision number or info']
       [, @datecreated='Date created']
       [, @datelastchanged='Date last changed']
Returns: (None)
$Workfile: sp_usage.sql $
$Author: Khen $. Email: khen@khen.com
$Revision: 7 $
Example: sp_usage @objectname='sp_who', @desc='Returns a list of currently running
 jobs', @parameters=[@loginname]
Created: 1992-04-03. $Modtime: 1/04/01 8:38p $.
*/
AS
SET NOCOUNT ON
IF (@objectname+@desc IS NULL) GOTO Help
PRINT 'Object: '+@objectname
PRINT 'Description: '+@desc
IF (OBJECTPROPERTY(OBJECT_ID(@objectname),'IsProcedure')=1)
OR (OBJECTPROPERTY(OBJECT_ID(@objectname),'IsExtendedProc')=1)
OR (OBJECTPROPERTY(OBJECT_ID(@objectname),'IsReplProc')=1)
OR (LOWER(LEFT(@objectname,3))='sp_') BEGIN -- Special handling for system
 procedures
 PRINT CHAR(13)+'Usage: '+@objectname+' '+@parameters
 PRINT CHAR(13)+'Returns: '+@returns
END
-- $NoKeywords: $ -- Prevents the keywords below from being expanded in VSS
IF (@workfile IS NOT NULL)
 PRINT CHAR(13)+'$Workfile: '+@workfile+' $'
IF (@author IS NOT NULL)
 PRINT CHAR(13)+'$Author: '+@author+' $. Email: '+@email
IF (@version IS NOT NULL)
 PRINT CHAR(13)+'$Revision: '+@version+'.'+@revision+' $'
IF (@example IS NOT NULL)
 PRINT CHAR(13)+'Example: '+@example
IF (@datecreated IS NOT NULL) BEGIN -- Crop time if it's midnight
 DECLARE @datefmt varchar(8000), @dc varchar(30), @lc varchar(30)
 SET @dc=CONVERT(varchar(30), @datecreated, 120)
 SET @lc=CONVERT(varchar(30), @datelastchanged, 120)
 PRINT CHAR(13)+'Created: '+CASE
DATEDIFF(ss,CONVERT(char(8),@datecreated,108),'00:00:00') WHEN 0 THEN
LEFT(@dc,10) ELSE @dc END
+'. $Modtime: '+CASE DATEDIFF(ss,CONVERT(char(8),@datelastchanged,108),'00:00:00')
 WHEN 0 THEN LEFT(@lc,10) ELSE @lc END+' $.'
END
RETURN 0
Help:
EXEC dbo.sp_usage @objectname='sp_usage',       -- Recursive call
     @desc='Provides usage information for stored procedures and descriptions 
of other types of objects',
     @parameters='@objectname=''ObjectName'', @desc=''Description of object''
       [, @parameters=''param1,param2...'']
       [, @example=''Example of usage'']
       [, @workfile=''File name of script'']
       [, @author=''Object author'']
       [, @email=''Author email'']
       [, @version=''Version number or info'']
       [, @revision=''Revision number or info'']
       [, @datecreated=''Date created'']
       [, @datelastchanged=''Date last changed'']',
     @example='sp_usage @objectname=''sp_who'',
     @desc=''Returns a list of currently running jobs'',
     @parameters=[@loginname]',
     @author='Ken Henderson',
     @workfile='sp_usage.sql',
     @email='khen@khen.com',
     @version='3', @revision='1',
     @datecreated='4/3/92', @datelastchanged='7/1/99'
RETURN -1
GO
EXEC dbo.sp_usage

By passing in the appropriate parameters, you can use sp_usage to report usage info for any procedure. Sp_usage even calls itself for that very purpose (that's why we receive the warning message: "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'sp_usage.' The stored procedure will still be created."). Because Transact-SQL doesn't support subroutines, sp_usage uses a GOTO label to place the help message at the end of the procedure. This approach allows code at the start of the procedure to check for invalid parameter values and to jump quickly to the usage routine if necessary.

Set the QUOTED_IDENTIFIER and ANSI_NULLS options before you execute CREATE PROCEDURE (in its own command batch) because they're reset to the values they had at the time the procedure was created when it's executed (their values are stored in the status column of the procedure's row in sysobjects). This change lasts only for the duration of the procedure; afterward, they're restored to whatever they were before you executed the procedure. Setting QUOTED_IDENTIFIER or ANSI_NULLS inside a stored procedure has no effect on the execution of the stored procedure. To see how this works, run the code in Listing 1–9 in Query Analyzer:

Listing 1–9 SET ANSI_NULLS has no effect inside a stored procedure.

USE tempdb
GO
SET ANSI_NULLS ON
GO
CREATE PROC testn
AS
SET ANSI_NULLS OFF
DECLARE @var int
SET @var=NULL
SELECT * FROM Northwind..Customers WHERE @var=NULL
GO
EXEC testn

(Results abridged)

CustomerID CompanyName               ContactName
---------- ------------------------- ----------------
(0 row(s) affected)

If ANSI_NULLS is actually off at the time of the SELECT, as the SET command inside the procedure specifies, the SELECT should return all the rows in the Northwind Customers table. As you can see, this is not what happens. Now change the SET ANSI_NULLS command that precedes the CREATE PROCEDURE to turn ANSI null handling OFF, and rerun the procedure. You should see all the rows in the Customers table listed.

Set environmental options (e.g., NOCOUNT, LOCK_TIMEOUT, and so on) that materially affect the procedure early on. It's a good practice to set them at the very start of the procedure so that they stand out to other developers.

Avoid broken ownership chains when dealing with stored procedures and the objects they reference. Try to ensure that the owner of a stored procedure and the owner of the objects it references are the same. The best way to do this is by specifying the dbo user as the owner of every object you create. Having multiple objects with the same name but different owners adds an unnecessary layer of indirection to the database that's almost always more trouble than it's worth. While perhaps useful during the development phase of a project, it's definitely something you should avoid on production servers.

When used within a stored procedure, certain commands require the objects they reference to be owner qualified (an object reference is said to be owner qualified when the object name is prefixed with the name of the owner and a period) if the procedure is to be executed by users other than the owner. These commands are

  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • CREATE INDEX
  • DROP INDEX
  • UPDATE STATISTICS
  • All DBCC commands

Don't use the sp_ prefix for anything but system procedures. Because of the confusion it can cause, avoid creating procedures in user databases with the sp_ prefix. Also, don't create nonsystem procedures in the master database. If a procedure is not a system procedure, it's likely that you don't need to put it in the master database in the first place.

Include USE dbname at the top of creation scripts for procedures that must reside in a specific database. This ensures that they end up where they belong and alleviates having to remember to set the current database context before executing the script.

Keep each stored procedure as simple and modular as possible. Ideally, a stored procedure will accomplish a single task or a small group of closely related tasks.

As a rule, SET NOCOUNT ON should be the first statement in every stored procedure you create because it minimizes network traffic between SQL Server and client applications. Setting NOCOUNT on disables DONE_IN_PROC messages—the messages SQL Server normally sends to the client indicating the number of rows affected by a T-SQL statement. Because these messages are very rarely used, eliminating them conserves network bandwidth without really giving up any functionality and can speed up applications considerably. Note that you can disable DONE_IN_PROC messages for the entire server via a trace flag (3640) and for a particular user session via the sp_configure 'user options' command. (In rare circumstances, disabling DONE_IN_PROC messages can cause problems with some applications—for example, some older versions of Microsoft Access and certain ill-behaved OLEDB providers).

Create a procedure using the WITH ENCRYPTION option if you want to keep its source code from being viewable by users. Don't delete it from syscomments. Doing so will render the procedure inaccessible and you'll have to drop and recreate it.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020