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

Chris Anderson Chris at activeide.com
Wed Feb 13 04:56:34 CST 2008

> 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!

cmd.ExecuteScalar() returns the first column of the first row of the result set that is returned from the SQL procedure.
You will need to change the SQL to something like
    SELECT COUNT(*) FROM table CategoryID IS NULL
(i.e. remove the use of the @NoCategory variable)
If you wanted to keep the SQL the same, I believe you need to add a SqlParameter to cmd with it's Direction set to SqlParameter.ReturnValue, then after executing and closing the command, read the return value from the parameter.
Hope that helps

More information about the thelist mailing list