Prev: Trigger to insert column value
Next: Timeout expired
From: ChrisB on 17 Jul 2008 13:31
Hello,

I recently converted a Sql Server 2000 database to 2005 (using the copy
database method). After conversion, all of the stored procedures executed
just as they had before . . . no problems. I have run the following issue,
however . . .

Although the following stored proc executed as expected following
conversion, after editing in Sql Server Management Studio, it no longer
works. By "editing", I simply mean that I executed the alter procedure code
generated when I chose to do a modification (I made no direct changes to the
sp code). It seems running the alter code somehow changed the sp in a way
that makes it no longer work correctly.

Specifically, when a valid @ProviderIDFilter is used, no records are
returned. I suspect the problem may be related to the use of NULL defaults
when declaring the prodedures parameters. Any thoughts?

Thanks!
Chris

-------------------------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



ALTER PROCEDURE [dbo].[UserListGet]

(@ProviderIDFilter [uniqueidentifier] = NULL,

@UserActiveStatusIDFilter [int] = NULL,

@RoleIDFilter [uniqueidentifier] = NULL,

@UnfilteredUsername [varchar](20) = NULL)

AS

SELECT DISTINCT

Users.Username,

Users.FirstName,

Users.LastName,

Roles.[Name] AS RoleName,

Users.UserActiveStatusID

FROM Users

INNER JOIN UserRoles ON Users.Username = UserRoles.Username

INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID

LEFT OUTER JOIN ProviderUsers ON Users.Username = ProviderUsers.Username

WHERE

-- Filtered records

(((ProviderUsers.ProviderID = @ProviderIDFilter) OR (@ProviderIDFilter =
NULL)) AND

((Users.UserActiveStatusID = @UserActiveStatusIDFilter) OR
(@UserActiveStatusIDFilter = NULL)) AND

((Roles.RoleID = @RoleIDFilter) OR (@RoleIDFilter = NULL)) AND

(Users.Username != 'System' AND Users.Username != 'Startup' AND
Users.Username != ''))

OR

-- Unfiltered records

(Users.Username = @UnfilteredUsername)


ORDER BY Users.Username




From: Russell Fields on 17 Jul 2008 14:10
Chris,

Throughout, your code is using this pattern:

(((ProviderUsers.ProviderID = @ProviderIDFilter)
OR (@ProviderIDFilter = NULL))

Equality comparison to NULL is not legal TSQL syntax any more. With more
strict ANSI regulations it never evalutes to TRUE.

From the Books Online: When SET ANSI_NULLS is ON, a comparison in which one
or more of the expressions is NULL does not yield either TRUE or FALSE; it
yields UNKNOWN. ... When ANSI_NULLS is OFF, comparisons such as ColumnA =
NULL return TRUE.

The correct answer is not to fall back on a special setting, but to adjust
the code to:

(((ProviderUsers.ProviderID = @ProviderIDFilter)
OR (@ProviderIDFilter IS NULL))

Altering the stored procedure changed the plan to match the current
ANSI_NULLS setting.

RLF

"ChrisB" <pleasereplytogroup(a)thanks.com> wrote in message
news:%23BJ7rLD6IHA.4852(a)TK2MSFTNGP03.phx.gbl...
> Hello,
>
> I recently converted a Sql Server 2000 database to 2005 (using the copy
> database method). After conversion, all of the stored procedures executed
> just as they had before . . . no problems. I have run the following
> issue, however . . .
>
> Although the following stored proc executed as expected following
> conversion, after editing in Sql Server Management Studio, it no longer
> works. By "editing", I simply mean that I executed the alter procedure
> code generated when I chose to do a modification (I made no direct changes
> to the sp code). It seems running the alter code somehow changed the sp
> in a way that makes it no longer work correctly.
>
> Specifically, when a valid @ProviderIDFilter is used, no records are
> returned. I suspect the problem may be related to the use of NULL
> defaults when declaring the prodedures parameters. Any thoughts?
>
> Thanks!
> Chris
>
> -------------------------------------------------------------------------------------------------------------------------------
>
> set ANSI_NULLS ON
>
> set QUOTED_IDENTIFIER ON
>
> go
>
>
>
> ALTER PROCEDURE [dbo].[UserListGet]
>
> (@ProviderIDFilter [uniqueidentifier] = NULL,
>
> @UserActiveStatusIDFilter [int] = NULL,
>
> @RoleIDFilter [uniqueidentifier] = NULL,
>
> @UnfilteredUsername [varchar](20) = NULL)
>
> AS
>
> SELECT DISTINCT
>
> Users.Username,
>
> Users.FirstName,
>
> Users.LastName,
>
> Roles.[Name] AS RoleName,
>
> Users.UserActiveStatusID
>
> FROM Users
>
> INNER JOIN UserRoles ON Users.Username = UserRoles.Username
>
> INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID
>
> LEFT OUTER JOIN ProviderUsers ON Users.Username = ProviderUsers.Username
>
> WHERE
>
> -- Filtered records
>
> (((ProviderUsers.ProviderID = @ProviderIDFilter) OR (@ProviderIDFilter =
> NULL)) AND
>
> ((Users.UserActiveStatusID = @UserActiveStatusIDFilter) OR
> (@UserActiveStatusIDFilter = NULL)) AND
>
> ((Roles.RoleID = @RoleIDFilter) OR (@RoleIDFilter = NULL)) AND
>
> (Users.Username != 'System' AND Users.Username != 'Startup' AND
> Users.Username != ''))
>
> OR
>
> -- Unfiltered records
>
> (Users.Username = @UnfilteredUsername)
>
>
> ORDER BY Users.Username
>
>
>
>


From: ChrisB on 18 Jul 2008 09:28
Thanks for the info, Russell. I replaced "=" with "IS" as you suggested,
and that did the trick.

Chris

"Russell Fields" <russellfields(a)nomail.com> wrote in message
news:eX$A4hD6IHA.4468(a)TK2MSFTNGP02.phx.gbl...
> Chris,
>
> Throughout, your code is using this pattern:
>
> (((ProviderUsers.ProviderID = @ProviderIDFilter)
> OR (@ProviderIDFilter = NULL))
>
> Equality comparison to NULL is not legal TSQL syntax any more. With more
> strict ANSI regulations it never evalutes to TRUE.
>
> From the Books Online: When SET ANSI_NULLS is ON, a comparison in which
> one or more of the expressions is NULL does not yield either TRUE or
> FALSE; it yields UNKNOWN. ... When ANSI_NULLS is OFF, comparisons such as
> ColumnA = NULL return TRUE.
>
> The correct answer is not to fall back on a special setting, but to adjust
> the code to:
>
> (((ProviderUsers.ProviderID = @ProviderIDFilter)
> OR (@ProviderIDFilter IS NULL))
>
> Altering the stored procedure changed the plan to match the current
> ANSI_NULLS setting.
>
> RLF
>
> "ChrisB" <pleasereplytogroup(a)thanks.com> wrote in message
> news:%23BJ7rLD6IHA.4852(a)TK2MSFTNGP03.phx.gbl...
>> Hello,
>>
>> I recently converted a Sql Server 2000 database to 2005 (using the copy
>> database method). After conversion, all of the stored procedures
>> executed just as they had before . . . no problems. I have run the
>> following issue, however . . .
>>
>> Although the following stored proc executed as expected following
>> conversion, after editing in Sql Server Management Studio, it no longer
>> works. By "editing", I simply mean that I executed the alter procedure
>> code generated when I chose to do a modification (I made no direct
>> changes to the sp code). It seems running the alter code somehow changed
>> the sp in a way that makes it no longer work correctly.
>>
>> Specifically, when a valid @ProviderIDFilter is used, no records are
>> returned. I suspect the problem may be related to the use of NULL
>> defaults when declaring the prodedures parameters. Any thoughts?
>>
>> Thanks!
>> Chris
>>
>> -------------------------------------------------------------------------------------------------------------------------------
>>
>> set ANSI_NULLS ON
>>
>> set QUOTED_IDENTIFIER ON
>>
>> go
>>
>>
>>
>> ALTER PROCEDURE [dbo].[UserListGet]
>>
>> (@ProviderIDFilter [uniqueidentifier] = NULL,
>>
>> @UserActiveStatusIDFilter [int] = NULL,
>>
>> @RoleIDFilter [uniqueidentifier] = NULL,
>>
>> @UnfilteredUsername [varchar](20) = NULL)
>>
>> AS
>>
>> SELECT DISTINCT
>>
>> Users.Username,
>>
>> Users.FirstName,
>>
>> Users.LastName,
>>
>> Roles.[Name] AS RoleName,
>>
>> Users.UserActiveStatusID
>>
>> FROM Users
>>
>> INNER JOIN UserRoles ON Users.Username = UserRoles.Username
>>
>> INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID
>>
>> LEFT OUTER JOIN ProviderUsers ON Users.Username = ProviderUsers.Username
>>
>> WHERE
>>
>> -- Filtered records
>>
>> (((ProviderUsers.ProviderID = @ProviderIDFilter) OR (@ProviderIDFilter =
>> NULL)) AND
>>
>> ((Users.UserActiveStatusID = @UserActiveStatusIDFilter) OR
>> (@UserActiveStatusIDFilter = NULL)) AND
>>
>> ((Roles.RoleID = @RoleIDFilter) OR (@RoleIDFilter = NULL)) AND
>>
>> (Users.Username != 'System' AND Users.Username != 'Startup' AND
>> Users.Username != ''))
>>
>> OR
>>
>> -- Unfiltered records
>>
>> (Users.Username = @UnfilteredUsername)
>>
>>
>> ORDER BY Users.Username
>>
>>
>>
>>
>
>


 | 
Pages: 1
Prev: Trigger to insert column value
Next: Timeout expired