[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