output parameters return NULL following a single NULL value

2 Sep 2005 3:09 AM

m9u35g

I am encountering weird behavior, and I wonder if it is well-known, if
it is a bug, or if it has a purpose of some sort.

In a stored procedure with output parameters, if a NULL value is
returned for an output parameter, all output parameters AFTER that
output parameter will be DEFAULT (or NULL if there is no default), even
if the table has values for them.

For example, in the stored procedure listed below, if LON_User.test is
NULL, every output parameter after that will return NULL, but every
output parameter before that (i.e., @USE_Firstname and @USE_LastName)
will be filled appropriately, despite the fact that USE_Email,
USE_Locked, and USE_Site all have non-null values in the row where
USE_UserID = 5062:

CREATE PROCEDURE dbo.LON_UserGet
        @USE_UserID int,
        @USE_Firstname varchar (150) = NULL OUTPUT,
        @test smalldatetime = NULL OUTPUT,
        @USE_LastName   varchar (150) = NULL OUTPUT,
        @USE_Email              varchar (75) = NULL OUTPUT,
        @USE_Locked             bit = NULL OUTPUT,
        @USE_Site       int = NULL OUTPUT

AS

SELECT
        @USE_Firstname = USE_Firstname,
        @test = LON_User.test,
        @USE_LastName = USE_LastName,
        @USE_Email = USE_Email,
        @USE_Locked = USE_Locked,
        @USE_Site = USE_Site

FROM LON_User
WHERE USE_UserID = @USE_UserID

Here is the output from a test run:

Running dbo."LON_UserGet" ( @USE_UserID = 5062, @USE_Firstname =
<DEFAULT>, @USE_LastName = <DEFAULT>, @test = <DEFAULT>, @USE_Email =
<DEFAULT>, @USE_Locked = <DEFAULT>, @USE_Site = <DEFAULT> ).

No rows affected.
No more results.
(0 row(s) returned)
@USE_Firstname = Agent
@USE_LastName = 007
@test = <DEFAULT>
@USE_Email = <DEFAULT>
@USE_Locked = <DEFAULT>
@USE_Site = <DEFAULT>
@RETURN_VALUE = 0
Finished running dbo."LON_UserGet".

Have you experienced this behavior before? Is it a bug? Why does this
happen?

Insight is appreciated. Thanks.

Justin Keyes

2 Sep 2005 3:14 AM

m9u35g

Ack. The previous message has an error:

        @test smalldatetime = NULL OUTPUT,
        @USE_LastName   varchar (150) = NULL OUTPUT,

should be changed to:

        @USE_LastName   varchar (150) = NULL OUTPUT,
        @test smalldatetime = NULL OUTPUT,

(@FirstName and @LastName are both in front of @test).

Thanks.

2 Sep 2005 4:10 AM

Steve Kass

Justin,

I cannot reproduce the problem you are having, at least not in
Query Analyzer. It looks like you are calling the procedure
from an ASP page or something similar, so can you try the
procedure call in Query Analyzer?

This would narrow down where the problem is, bug or
no bug.

Other than that, here are the usual disclaimers: are you up-to-date
with service packs? For SQL Server, tell us the output you get
from SELECT @@version.

Can you post your code, particularly any part that declares the
parameters and calls the procedure?

Here is the repro I wrote attempting to repro the bug, followed
by the (correct) results I get in Query Analyzer, and a note of
what I would expect if this bug showed up. Please correct me
if I'm wrong about what I should be expecting.

use tempdb
go

create table T (
pk int,
h varchar(10),
i smalldatetime,
j int
)
go

insert into T values (1,'a',1,2)
insert into T values (2,'b',null,3)
insert into T values (3,'c',4,null)
go

create proc p (
@pk int,
@h varchar(10) output,
@i smalldatetime output,
@j int output
) as
select
    @h = h,
    @i = i,
    @j = j
from T
where pk = @pk
go

declare @h varchar(10), @i smalldatetime, @j int
exec p 1, @h output, @i output, @j output
select @h, @i, @j
exec p 2, @h output, @i output, @j output
select @h, @i, @j
exec p 3, @h output, @i output, @j output
select @h, @i, @j
go

/*
Output:
a          1900-01-02 00:00:00                                    2
b          NULL                                                   3
c          1900-01-05 00:00:00                                    NULL

If I had reproed what you found, the output of
the second call would be (b, NULL, NULL).

*/

drop proc p
drop table T

Steve Kass
Drew University

m9u***@gmail.com wrote:

Show quote

>I am encountering weird behavior, and I wonder if it is well-known, if
>it is a bug, or if it has a purpose of some sort.
>
>In a stored procedure with output parameters, if a NULL value is
>returned for an output parameter, all output parameters AFTER that
>output parameter will be DEFAULT (or NULL if there is no default), even
>if the table has values for them.
>
>For example, in the stored procedure listed below, if LON_User.test is
>NULL, every output parameter after that will return NULL, but every
>output parameter before that (i.e., @USE_Firstname and @USE_LastName)
>will be filled appropriately, despite the fact that USE_Email,
>USE_Locked, and USE_Site all have non-null values in the row where
>USE_UserID = 5062:
>
>CREATE PROCEDURE dbo.LON_UserGet
>        @USE_UserID int,
>        @USE_Firstname varchar (150) = NULL OUTPUT,
>        @test smalldatetime = NULL OUTPUT,
>        @USE_LastName   varchar (150) = NULL OUTPUT,
>        @USE_Email              varchar (75) = NULL OUTPUT,
>        @USE_Locked             bit = NULL OUTPUT,
>        @USE_Site       int = NULL OUTPUT
>
>AS
>
>SELECT
>        @USE_Firstname = USE_Firstname,
>        @test = LON_User.test,
>        @USE_LastName = USE_LastName,
>        @USE_Email = USE_Email,
>        @USE_Locked = USE_Locked,
>        @USE_Site = USE_Site
>
>FROM LON_User
>WHERE USE_UserID = @USE_UserID
>
>Here is the output from a test run:
>
>Running dbo."LON_UserGet" ( @USE_UserID = 5062, @USE_Firstname =
><DEFAULT>, @USE_LastName = <DEFAULT>, @test = <DEFAULT>, @USE_Email =
><DEFAULT>, @USE_Locked = <DEFAULT>, @USE_Site = <DEFAULT> ).
>
>No rows affected.
>No more results.
>(0 row(s) returned)
>@USE_Firstname = Agent
>@USE_LastName = 007
>@test = <DEFAULT>
>@USE_Email = <DEFAULT>
>@USE_Locked = <DEFAULT>
>@USE_Site = <DEFAULT>
>@RETURN_VALUE = 0
>Finished running dbo."LON_UserGet".
>
>Have you experienced this behavior before? Is it a bug? Why does this
>happen?
>
>Insight is appreciated. Thanks.
>
>Justin Keyes
>
>
>

2 Sep 2005 9:50 PM

Justin

You are right. It was a problem with my application code. Thanks for
your detailed help, it pointed me in the right direction.

3 Sep 2005 4:55 AM

Steve Kass

Thanks for making this thread more useful with the
followup. I'm glad you were able to track down the problem.

SK

Justin wrote:

Show quote

>You are right. It was a problem with my application code. Thanks for
>your detailed help, it pointed me in the right direction.
>
>
>