2.0: ExecuteScalar returns null

[RAM]

Hello,
I am learning .NET 2.0. I am trying to call a stored procedure:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "NextDocumentNumber";
cmd.Transaction = t;
cmd.Connection = c;
int n = (int)cmd.ExecuteScalar(); // here problem

The problem is that ExecuteScalar returns null although executed in SQL
Server Management Studio it returns some integer value. I don't understand
why because transaction t and connection c are correct. Below the code of
stored procedure:

ALTER PROCEDURE [dbo].[NextDocumentNumber]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @n int

SELECT @n = COUNT(*) FROM Documents

IF @n = 0

BEGIN

SELECT @n =StartOfRange FROM Numbering

WHERE Year = YEAR(GETDATE())

RETURN @n

END

SELECT @n = MAX(Number) FROM Documents

WHERE Year = YEAR(GETDATE()) AND Movement = 'PZ'

RETURN @n + 1

END

Could you help me please to solve the problem? Thank you very much!

/RAM/

 

[David Browne]

Hello,
I am learning .NET 2.0. I am trying to call a stored procedure:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "NextDocumentNumber";
cmd.Transaction = t;
cmd.Connection = c;
int n = (int)cmd.ExecuteScalar(); // here problem

ExecuteScalar returns the first column of the first row of the first
resultset returned by the command. Your procedure uses a return value, not
a resultset.

Furthermore, it's bad form to use the return value of a stored procedure to
return data. By convention the return value of a stored procedure is used
to return a success or failure code. If you want to return data, use an
output parameter or a resultset.

Also this procedure is not even correctly coded. To return a sequence value
based on existing data requires careful transaction handling. See "Inside
Microsoft SQL Server 2005: T-SQL Programming pp428 et seq" for a complete
treatment. Or consider simply using an IDENTITY column.

David

 

[David Browne]

ExecuteScalar returns the first column of the first row of the first
resultset returned by the command. Your procedure uses a return value,
not a resultset.

Furthermore, it's bad form to use the return value of a stored procedure
to return data. By convention the return value of a stored procedure is
used to return a success or failure code. If you want to return data, use
an output parameter or a resultset.

Also this procedure is not even correctly coded. To return a sequence
value based on existing data requires careful transaction handling. See
"Inside Microsoft SQL Server 2005: T-SQL Programming pp428 et seq" for a
complete treatment. Or consider simply using an IDENTITY column.

sb

Inside
Microsoft SQL Server 2005: T-SQL Querying pp 428 et seq

David

 

[RAM]

Also this procedure is not even correctly coded. To return a sequence
I don't have "Inside Microsoft SQL Server 2005". Could you describe me
please the solution in a few words?
Thank you!

 

[David Browne]

I don't have "Inside Microsoft SQL Server 2005". Could you describe me
please the solution in a few words?
Thank you!

Use an Identity column.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_3iex.asp

David

 

[RAM]

Use an Identity column.

I cannot because 'seed' value in my application is taken from special table.
Is the second solution complicated?

 

[msnews.microsoft.com]

ExecuteScalar() will not work with a return value. You can get the return
value as a parameter, however, if you explicitly declare it. If you want to
use ExecuteScalar(), use SELECT on the value instead of return.

What are you attempting here, however? If this is an identity value, just
return SCOPE_IDENTITY. If not, why are you not using an Identity value?

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************