Environmental Issues

for RuBoard

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 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          14.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.

for RuBoard