NULL Values in variables - Stored Procedures, Triggers, & Functions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » NULL Values in variables

NULL Values in variables

Options

Previous Topic · Next Topic

wdismukes

Posted: Sunday, October 07, 2007 12:47:38 PM

Rank: PFY

Joined: 5/3/2007
Posts: 20
Points: -37
Location: Houston, Texas

I was reading two articles about Nulls by Michael Coles on SQLServerCentral.com this morning. (I really ought to get a life!) He briefly discussed Null values in variables and gave an example. Now I have a question. How would you handle a null value for @Region in the following Stored Procedure without messing with the ANSI_NULLS setting?
USE Northwind
GO
CREATE PROC GetEmployeesByRegion
@MyRegion NVARCHAR(15)
AS
SET ANSI_NULLS ON
SELECT LastName, Region FROM Employees
WHERE Region = @MyRegion
GO
EXEC GetEmployeesByRegion @MyRegion = NULL -- I really want to return records where Region is null
GO
SELECT LastName, Region
FROM Employees
WHERE Region IS NULL

Executing the Procedure returns no rows. Executing the query returns four rows.

Scott Whigham

Posted: Wednesday, October 10, 2007 10:33:14 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Okay - what you have to do here is to SET ANSI_NULLS OFF in the connection that creates the proc (tricky, right?).

Quote:

USE Northwind
GO
SET ANSI_NULLS OFF
GO
CREATE PROC GetEmployeesByRegion
@MyRegion NVARCHAR(15)
AS
SELECT LastName, Region FROM Employees
WHERE Region = @MyRegion
GO

Now, go open a new window and execute

Quote:

EXEC GetEmployeesByRegion @MyRegion = NULL

DBCC USEROPTIONS is a good command to run that will show you what options are set for your connection.

wdismukes

Posted: Sunday, October 14, 2007 8:16:58 AM

Rank: PFY

Joined: 5/3/2007
Posts: 20
Points: -37
Location: Houston, Texas

Well Shazam! and Goll-leee. It works! Who would have thought to do that! I'm so excited that I think I will go right now and renew one of my subscriptions to the LearnItFirst.com. I'll even use the $50 certificate I won in your Survey Giveaway.

Thanks Scott.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.