This chapter is from the book
This chapter is from the book
This chapter is from the book
Environmental Issues
A number of SQL Server environmental settings affect the behavior of stored procedures. You specify most of these via SET commands. They control the way that stored procedures handle nulls, quotes, cursors, BLOB fields, and so forth. Two of these—QUOTED_IDENTIFIER and ANSI_NULLS—are stored permanently in each procedure's status field in sysobjects, as I mentioned earlier in the chapter. That is, when you create a stored procedure, the status of these two settings is stored along with it. QUOTED_IDENTIFIER controls whether strings within double quotes are interpreted as object identifiers (e.g., table or column references), and ANSI_NULLS controls whether non-ANSI equality comparisons with NULLs are allowed.
SET QUOTED_IDENTIFIER is normally used with a stored procedure to allow the procedure to reference objects with names that contain reserved words, spaces, or other illegal characters. An example is provided in Listing 1–20.
Listing 1–20 SET QUOTED_IDENTIFIER allows references to objects with names with embedded spaces.
USE Northwind
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.listorders') IS NOT NULL
DROP PROC dbo.listorders
GO
CREATE PROC dbo.listorders
AS
SELECT * FROM "Order Details"
GO
SET QUOTED_IDENTIFIER OFF
GO
EXEC dbo.listorders
(Results abridged)
OrderID ProductID UnitPrice Quantity Discount ----------- ----------- --------------------- -------- ------------- 10248 11 4.0000 12 0.0 10248 42 9.8000 10 0.0 10248 72 34.8000 5 0.0 10249 14 18.6000 9 0.0 10249 51 42.4000 40 0.0 10250 41 7.7000 10 0.0
"Order Details" contains both a reserved word and a space, so it can't be referenced without special handling. In this case, we turned on quoted identifier support and enclosed the table name in double quotes, but a better way would be to use SQL Server's square brackets ( [ ] ) to enclose the name (e.g., [Order Details]) because this alleviates the need to change any settings. Note that bracketed object names are not supported by the ANSI/ISO SQL standard.
The ANSI_NULLS setting is even more useful to stored procedures. It controls whether non-ANSI equality comparisons with NULLs work properly. This is particularly important with stored procedure parameters that can receive NULL values. See Listing 1–21 for an example:
Listing 1–21 SET ANSI_NULLS allows comparisons between variables or columns and NULL values to work as you would expect.
USE Northwind
IF (OBJECT_ID('dbo.ListRegionalEmployees') IS NOT NULL)
DROP PROC dbo.ListRegionalEmployees
GO
SET ANSI_NULLS OFF
GO
CREATE PROC dbo.ListRegionalEmployees @region nvarchar(30)
AS
SELECT EmployeeID, LastName, FirstName, Region FROM employees
WHERE Region=@region
GO
SET ANSI_NULLS ON
GO
EXEC dbo.ListRegionalEmployees NULL
(Results)
EmployeeID LastName FirstName Region ----------- -------------------- ---------- --------------- 5 Buchanan Steven NULL 6 Suyama Michael NULL 7 King Robert NULL 9 Dodsworth Anne NULL
Thanks to SET ANSI_NULLS, the procedure can successfully compare a NULL @region with the region column in the Northwind Employees table. The query returns the rows that have NULL region values because, contrary to the ANSI SQL specification, SQL Server checks the NULL variable against the column for equality. The handiness of this becomes more evident when a procedure defines a large number of "NULL-able" parameters. Without the ability to test NULL values for equality in a manner identical to non-NULL values, each NULL-able parameter would require special handling (perhaps using the IS NULL predicate), very likely multiplying the amount of code necessary to process query parameters.
Because SQL Server stores the QUOTED_IDENTIFIER and ANSI_NULLS settings with each stored procedure, you can trust them to have the values you require when a procedure runs. The server restores them to the values they had when the procedure was created each time the procedure runs, then resets them afterward. Here's an example:
SET ANSI_NULLS ON EXEC dbo.ListRegionalEmployees NULL
The stored procedure still executes as though ANSI_NULLS is set to OFF. Note that you can check the saved status of a procedure's QUOTED_IDENTIFIER and ANSI_NULLS settings via the OBJECTPROPERTY() function. An example is provided in Listing 1–22:
Listing 1–22 You can check the ANSI_NULLS and QUOTED_IDENTIFIER status for a procedure using the OBJECTPROPERTY function.
USE Northwind
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.ListRegionalEmployees'), 'ExecIsAnsiNullsOn')
AS 'AnsiNulls'
(Results)
AnsiNulls ----------- 0
A number of other environmental commands affect how stored procedures execute. SET XACT_ABORT, SET CURSOR_CLOSE_ON_COMMIT, SET TEXTSIZE, SET IMPLICIT_TRANSACTIONS, and numerous others help determine how a stored procedure behaves when executed. If you have a stored procedure that requires a SET command to have a particular value to run properly, set it to that value as early as possible in the procedure and document why it's necessary via comments.