but in ado.net handling an output parameter with an initial value NULL?

[z f]

hi,

i use ado.net to call a stored procedure in SQL Server 2000.
the SP have a parameter that is input and output

when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to something
else.

if the initial value is not null, the return value of the output parameter
is received correctly.

what's the problem???

TIA, z.

 

[Earl]

Depends on how the "SP set the value to something else", so it sounds like
the problem is in the SP. You might want to post that here and/or on the
microsoft.public.sqlserver forums.

 

[z f]

the SP looks like that:
(but again i don't think it has a problem, since when the output parameter
value is not null, it returns correctly. when it is null it returns null.)

ALTER procedure Login
(
@email varchar (100),
@password varchar (20),
@user_id int output,
@roles nvarchar (100) output
)
as
select
@roles = roles ,
@user_id = [user_id] ,
from users
where @email = email
and @password = password
and enabled = 1

 

[Earl]

If you run that in the QA will it execute with the comma after [user_id]?

In any event, you had initially posted:

"when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to something
else." But you also say, "when the output parameter value is not null, it
returns correctly. when it is null, it returns null."

I'm not clear what input value of null that you are talking about is being
set for what parameters?

the SP looks like that:
(but again i don't think it has a problem, since when the output parameter
value is not null, it returns correctly. when it is null it returns null.)

ALTER procedure Login
(
@email varchar (100),
@password varchar (20),
@user_id int output,
@roles nvarchar (100) output
)
as
select
@roles = roles ,
@user_id = [user_id] ,
from users
where @email = email
and @password = password
and enabled = 1

Depends on how the "SP set the value to something else", so it sounds
like
the problem is in the SP. You might want to post that here and/or on the
microsoft.public.sqlserver forums.

 

[z. f.]

if for the @user_id i set to dbnull.value when i call the SP ( never mind the logic that in this case i don't check this value, in other SP i do check the value of an input-output parameter.)
i can't get the return value correctly.

If you run that in the QA will it execute with the comma after [user_id]?

In any event, you had initially posted:

"when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to something
else." But you also say, "when the output parameter value is not null, it
returns correctly. when it is null, it returns null."

I'm not clear what input value of null that you are talking about is being
set for what parameters?

the SP looks like that:
(but again i don't think it has a problem, since when the output parameter
value is not null, it returns correctly. when it is null it returns null.)

ALTER procedure Login
(
@email varchar (100),
@password varchar (20),
@user_id int output,
@roles nvarchar (100) output
)
as
select
@roles = roles ,
@user_id = [user_id] ,
from users
where @email = email
and @password = password
and enabled = 1

Depends on how the "SP set the value to something else", so it sounds
like
the problem is in the SP. You might want to post that here and/or on the
microsoft.public.sqlserver forums.

 

[Earl]

Use the Profiler when you run your SP and check the parameters. That should
show you where the problem lies.


if for the @user_id i set to dbnull.value when i call the SP ( never mind
the logic that in this case i don't check this value, in other SP i do check
the value of an input-output parameter.)
i can't get the return value correctly.

If you run that in the QA will it execute with the comma after [user_id]?

In any event, you had initially posted:

"when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to something
else." But you also say, "when the output parameter value is not null, it
returns correctly. when it is null, it returns null."

I'm not clear what input value of null that you are talking about is being
set for what parameters?

the SP looks like that:
(but again i don't think it has a problem, since when the output parameter
value is not null, it returns correctly. when it is null it returns null.)

ALTER procedure Login
(
@email varchar (100),
@password varchar (20),
@user_id int output,
@roles nvarchar (100) output
)
as
select
@roles = roles ,
@user_id = [user_id] ,
from users
where @email = email
and @password = password
and enabled = 1

Depends on how the "SP set the value to something else", so it sounds
like
the problem is in the SP. You might want to post that here and/or on the
microsoft.public.sqlserver forums.

 

[z. f.]

the problem is in ADO.NET, because if i run the query from the profiler in query analyzer, the results are OK.
actually the ADO.NET sends to the SQL the parameters without getting it's return value.

for example, the query without setting the output parameter to null before the call looks like that:
on the RPC:Starting event

declare @P1 varchar(50)
set @P1=NULL
exec MySp @a = N'val', @ret = @P1 output
select @P1


the query looks defferently if the only little change that i do before the call is setting the output parameter to null (whether i change it's type to input-output or just leaves it as output parameter):

exec MySp @a = N'val', @ret = NULL

well i guess it can explain why the return value don't come back, but why in the first place the query should be changed because of setting an initial value to an output parameter ???


TIA, z.






Use the Profiler when you run your SP and check the parameters. That should
show you where the problem lies.


if for the @user_id i set to dbnull.value when i call the SP ( never mind
the logic that in this case i don't check this value, in other SP i do check
the value of an input-output parameter.)
i can't get the return value correctly.

If you run that in the QA will it execute with the comma after [user_id]?

In any event, you had initially posted:

"when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to something
else." But you also say, "when the output parameter value is not null, it
returns correctly. when it is null, it returns null."

I'm not clear what input value of null that you are talking about is being
set for what parameters?

the SP looks like that:
(but again i don't think it has a problem, since when the output parameter
value is not null, it returns correctly. when it is null it returns null.)

ALTER procedure Login
(
@email varchar (100),
@password varchar (20),
@user_id int output,
@roles nvarchar (100) output
)
as
select
@roles = roles ,
@user_id = [user_id] ,
from users
where @email = email
and @password = password
and enabled = 1

Depends on how the "SP set the value to something else", so it sounds
like
the problem is in the SP. You might want to post that here and/or on the
microsoft.public.sqlserver forums.

 

[Earl]

I don't know the answer to that (one of the folks in the SQL Server
programming forum would, see microsoft.public.sqlserver.programming). It
might also help to see a small segment of code on how you are setting the
parameters in .Net.


the problem is in ADO.NET, because if i run the query from the profiler in
query analyzer, the results are OK.
actually the ADO.NET sends to the SQL the parameters without getting it's
return value.

for example, the query without setting the output parameter to null before
the call looks like that:
on the RPC:Starting event

declare @P1 varchar(50)
set @P1=NULL
exec MySp @a = N'val', @ret = @P1 output
select @P1


the query looks defferently if the only little change that i do before the
call is setting the output parameter to null (whether i change it's type to
input-output or just leaves it as output parameter):

exec MySp @a = N'val', @ret = NULL

well i guess it can explain why the return value don't come back, but why in
the first place the query should be changed because of setting an initial
value to an output parameter ???


TIA, z.






Use the Profiler when you run your SP and check the parameters. That should
show you where the problem lies.


if for the @user_id i set to dbnull.value when i call the SP ( never mind
the logic that in this case i don't check this value, in other SP i do check
the value of an input-output parameter.)
i can't get the return value correctly.

If you run that in the QA will it execute with the comma after [user_id]?

In any event, you had initially posted:

"when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to something
else." But you also say, "when the output parameter value is not null, it
returns correctly. when it is null, it returns null."

I'm not clear what input value of null that you are talking about is being
set for what parameters?

the SP looks like that:
(but again i don't think it has a problem, since when the output parameter
value is not null, it returns correctly. when it is null it returns null.)

ALTER procedure Login
(
@email varchar (100),
@password varchar (20),
@user_id int output,
@roles nvarchar (100) output
)
as
select
@roles = roles ,
@user_id = [user_id] ,
from users
where @email = email
and @password = password
and enabled = 1

Depends on how the "SP set the value to something else", so it sounds
like
the problem is in the SP. You might want to post that here and/or on the
microsoft.public.sqlserver forums.

 

[z f]

did you try it?

I don't know the answer to that (one of the folks in the SQL Server
programming forum would, see microsoft.public.sqlserver.programming). It
might also help to see a small segment of code on how you are setting the
parameters in .Net.


the problem is in ADO.NET, because if i run the query from the profiler in
query analyzer, the results are OK.
actually the ADO.NET sends to the SQL the parameters without getting it's
return value.

for example, the query without setting the output parameter to null before
the call looks like that:
on the RPC:Starting event

declare @P1 varchar(50)
set @P1=NULL
exec MySp @a = N'val', @ret = @P1 output
select @P1


the query looks defferently if the only little change that i do before the
call is setting the output parameter to null (whether i change it's type to
input-output or just leaves it as output parameter):

exec MySp @a = N'val', @ret = NULL

well i guess it can explain why the return value don't come back, but why in
the first place the query should be changed because of setting an initial
value to an output parameter ???


TIA, z.






Use the Profiler when you run your SP and check the parameters. That should
show you where the problem lies.


if for the @user_id i set to dbnull.value when i call the SP ( never mind
the logic that in this case i don't check this value, in other SP i do check
the value of an input-output parameter.)
i can't get the return value correctly.

If you run that in the QA will it execute with the comma after [user_id]?

In any event, you had initially posted:

"when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to something
else." But you also say, "when the output parameter value is not null, it
returns correctly. when it is null, it returns null."

I'm not clear what input value of null that you are talking about is being
set for what parameters?

the SP looks like that:
(but again i don't think it has a problem, since when the output parameter
value is not null, it returns correctly. when it is null it returns null.)

ALTER procedure Login
(
@email varchar (100),
@password varchar (20),
@user_id int output,
@roles nvarchar (100) output
)
as
select
@roles = roles ,
@user_id = [user_id] ,
from users
where @email = email
and @password = password
and enabled = 1

Depends on how the "SP set the value to something else", so it sounds
like
the problem is in the SP. You might want to post that here and/or on the
microsoft.public.sqlserver forums.

hi,

i use ado.net to call a stored procedure in SQL Server 2000.
the SP have a parameter that is input and output

when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to
something
else.

if the initial value is not null, the return value of the output parameter
is received correctly.

what's the problem???

TIA, z.

 

[Earl]

I did not run the "MySP" (you didn't give me the sproc or the table), but I
did create the User table and run the Login sproc. Both output parameters
returned correct (sample) values once I removed the comma following
[user_id]. But not every time. As a test; run your SP 6 times in succession,
using either <NULL> or an expected value for your output parameters.

did you try it?

I don't know the answer to that (one of the folks in the SQL Server
programming forum would, see microsoft.public.sqlserver.programming). It
might also help to see a small segment of code on how you are setting the
parameters in .Net.


the problem is in ADO.NET, because if i run the query from the profiler
in
query analyzer, the results are OK.
actually the ADO.NET sends to the SQL the parameters without getting it's
return value.

for example, the query without setting the output parameter to null
before
the call looks like that:
on the RPC:Starting event

declare @P1 varchar(50)
set @P1=NULL
exec MySp @a = N'val', @ret = @P1 output
select @P1


the query looks defferently if the only little change that i do before
the
call is setting the output parameter to null (whether i change it's type to
input-output or just leaves it as output parameter):

exec MySp @a = N'val', @ret = NULL

well i guess it can explain why the return value don't come back, but why in
the first place the query should be changed because of setting an initial
value to an output parameter ???


TIA, z.






Use the Profiler when you run your SP and check the parameters. That should
show you where the problem lies.


if for the @user_id i set to dbnull.value when i call the SP ( never mind
the logic that in this case i don't check this value, in other SP i do check
the value of an input-output parameter.)
i can't get the return value correctly.

If you run that in the QA will it execute with the comma after [user_id]?

In any event, you had initially posted:

"when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to something
else." But you also say, "when the output parameter value is not null, it
returns correctly. when it is null, it returns null."

I'm not clear what input value of null that you are talking about is
being
set for what parameters?

the SP looks like that:
(but again i don't think it has a problem, since when the output parameter
value is not null, it returns correctly. when it is null it returns null.)

ALTER procedure Login
(
@email varchar (100),
@password varchar (20),
@user_id int output,
@roles nvarchar (100) output
)
as
select
@roles = roles ,
@user_id = [user_id] ,
from users
where @email = email
and @password = password
and enabled = 1



Depends on how the "SP set the value to something else", so it sounds
like
the problem is in the SP. You might want to post that here and/or on the
microsoft.public.sqlserver forums.

hi,

i use ado.net to call a stored procedure in SQL Server 2000.
the SP have a parameter that is input and output

when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to
something
else.

if the initial value is not null, the return value of the output
parameter
is received correctly.

what's the problem???

TIA, z.

 

[z. f.]

actually it seems to be my fault.
i use a SQL Helper class.
that because of a problem of setting the parameter value to NULL in the initialization line handles the null case differently, and i didn't updated the direction property in this case.
sorry.
the helper function gets a parameters array and created a command it does:

For i = LBound(spParams) To UBound(spParams)
If spParams(i).Value Is DBNull.Value Then
'Throw New Exception("ParameterName(" & i.ToString() & "):" & spParams(i).ParameterName)
Dim param As SqlParameter = SQLCmd.Parameters.Add(spParams(i).ParameterName, spParams(i).DbType)
param.Value = DBNull.Value
param.Direction = spParams(i).Direction
Else
SQLCmd.Parameters.Add(spParams(i))
End If

and the If is only for the case of a null value because the line in the else block fails for a parameter with a null value (why???)
so i had to create the parameter differently and set the value in a different way and the direction property was forgoten.
maybe i still missing some properties this way.

thanks anyway.

I did not run the "MySP" (you didn't give me the sproc or the table), but I
did create the User table and run the Login sproc. Both output parameters
returned correct (sample) values once I removed the comma following
[user_id]. But not every time. As a test; run your SP 6 times in succession,
using either <NULL> or an expected value for your output parameters.

did you try it?

I don't know the answer to that (one of the folks in the SQL Server
programming forum would, see microsoft.public.sqlserver.programming). It
might also help to see a small segment of code on how you are setting the
parameters in .Net.


the problem is in ADO.NET, because if i run the query from the profiler
in
query analyzer, the results are OK.
actually the ADO.NET sends to the SQL the parameters without getting it's
return value.

for example, the query without setting the output parameter to null
before
the call looks like that:
on the RPC:Starting event

declare @P1 varchar(50)
set @P1=NULL
exec MySp @a = N'val', @ret = @P1 output
select @P1


the query looks defferently if the only little change that i do before
the
call is setting the output parameter to null (whether i change it's type to
input-output or just leaves it as output parameter):

exec MySp @a = N'val', @ret = NULL

well i guess it can explain why the return value don't come back, but why in
the first place the query should be changed because of setting an initial
value to an output parameter ???


TIA, z.






Use the Profiler when you run your SP and check the parameters. That should
show you where the problem lies.


if for the @user_id i set to dbnull.value when i call the SP ( never mind
the logic that in this case i don't check this value, in other SP i do check
the value of an input-output parameter.)
i can't get the return value correctly.

If you run that in the QA will it execute with the comma after [user_id]?

In any event, you had initially posted:

"when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to something
else." But you also say, "when the output parameter value is not null, it
returns correctly. when it is null, it returns null."

I'm not clear what input value of null that you are talking about is
being
set for what parameters?

the SP looks like that:
(but again i don't think it has a problem, since when the output parameter
value is not null, it returns correctly. when it is null it returns null.)

ALTER procedure Login
(
@email varchar (100),
@password varchar (20),
@user_id int output,
@roles nvarchar (100) output
)
as
select
@roles = roles ,
@user_id = [user_id] ,
from users
where @email = email
and @password = password
and enabled = 1



Depends on how the "SP set the value to something else", so it sounds
like
the problem is in the SP. You might want to post that here and/or on the
microsoft.public.sqlserver forums.

hi,

i use ado.net to call a stored procedure in SQL Server 2000.
the SP have a parameter that is input and output

when i send an input value of null, the return value does not return
correctly and i get null in return even if the SP set the value to
something
else.

if the initial value is not null, the return value of the output
parameter
is received correctly.

what's the problem???

TIA, z.