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

[Chris Anderson]

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