[thelist] .NET: Can't get Stored Proc to Return a Value

Ken Schaefer Ken at adOpenStatic.com
Tue Feb 12 22:48:26 CST 2008


You are setting a return value. Instead you either want to select a resultset, or your want to set an output parameter.

And best practise would be to use the Return value for error conditions (because that's what SQL Server uses them for), not as a cheap-n-nasty way of getting a piece of information out of the database.

Cheers
Ken

-----Original Message-----
From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Casey Crookston
Sent: Wednesday, 13 February 2008 3:03 PM
To: thelist at lists.evolt.org
Subject: [thelist] .NET: Can't get Stored Proc to Return a Value

Hi,

I'm having trouble getting a stored procedure to return a single integer
value.  Here's a short version:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Perm_Import_CJ]
AS

/* bunch of stuff removed */

DECLARE @NoCategory int
SELECT @NoCategory = COUNT(*) FROM table CategoryID IS NULL
/* print @NoCategory */

RETURN @NoCategory
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If I uncomment "print @NoCategory" it prints exactly the number it's
supposed to, so there is no problem with any of the queries in the stored
procedure.  Then, in the code, this is what 'm doing:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim dbConn As New
SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionName").ConnectionString)

Dim cmd As New SqlCommand("StoredProc", dbConn)
cmd.CommandType = CommandType.StoredProcedure
dbConn.Open()
Dim intNoCategory As Integer = CInt(cmd.ExecuteScalar())
dbConn.Close()
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

But, intNoCategory  ALWAYS returns a zero.

Not sure what I am doing wrong.  Thanks in advance for any help!

Casey


--

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !



More information about the thelist mailing list