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 theseQUOTED_IDENTIFIER and ANSI_NULLSare 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 120.
Listing 120 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
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 121 for an example:
Listing 121 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
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 122:
Listing 122 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'
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.