# stored procedure with return value

[jimsurf]

I am currently trying to figure out how to use a stored procedure that will return the result of a count query, and I am having some trouble.
Here is the stored procedure:

CREATE PROCEDURE qryVerifyUser
@user_mail varchar(15),
@user_password varchar(15),
@user_count int output
AS
SELECT @user_count =( SELECT Count(*) from new_user
where user_mail=@user_mail
and user_password=@user_password)
GO

The object is to pass the login info from a form and return the count as an int. The code for the aspx page looks like this:

<script language=&quot;c#&quot; runat=&quot;server&quot;>
void Page_Load(){
if (Page.IsPostBack){
if (Request.Form[&quot;Email.Text&quot;] != &quot;&quot{
SqlConnection aConnection = new SqlConnection
(
System.Configuration.ConfigurationSettings.AppSettings.Get(&quot;SurfJob&quot
);
SqlCommand aCommand = new SqlCommand();
aCommand.CommandText = &quot;dbo.[qryVerifyUser]&quot;;
aCommand.CommandType = System.Data.CommandType.StoredProcedure;
aCommand.Parameters.Add(new SqlParameter(&quot;@user_mail&quot;, Request.Form[&quot;Email&quot;]));
aCommand.Parameters.Add(new SqlParameter(&quot;@user_password&quot;, Request.Form[&quot;Password&quot;]));
aCommand.Parameters.Add(new SqlParameter(&quot;@user_count&quot;, 1));

aConnection.Open();
aCommand.Connection = aConnection;
aCommand.ExecuteReader();
Response.Write(&quot;__&quot; + aCommand.Parameters[&quot;@user_count&quot;].Value);
aConnection.Close();


The last add new parameter is the @user_count, but it doesn't return the value from the query. It only retruns the initial value. If I remove the initial value, the code fails. I have seen a bunch of code that sets up a parameter as a return value, but none of the examples I have tried work.

please help.

Jim

 

[wickerman]

Maybe you need to set the direction of your parameters before you execute the sproc. In this Windows Form example, Form1 contains button1, textBox1, sqlConnection1, and sqlCommand1. The sproc used in this example is really basic:

create procedure dbo.nw_CountProductsInACategory(
        @CategoryID int,
        @CountProducts int output
)
AS
SET @CountProducts = (SELECT Count(ProductID) As CountProducts
FROM Products
WHERE CategoryID = @CategoryID)

GO

and it uses the Northwind database running on a local SQL Server/MSDE. The button1_Click event (successfully) passes the value of the output parameter @CountProducts to textBox1.Text.

private void button1_Click(object sender, System.EventArgs e)
{
    sqlConnection1.ConnectionString = @"server=(local);Trusted_Connection=yes;database=northwind";
    sqlConnection1.Open();
    sqlCommand1.Connection = sqlConnection1;
    sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure;
    sqlCommand1.CommandText = "nw_CountProductsInACategory";
    sqlCommand1.Parameters.Add ("@CategoryID", SqlDbType.Int);
    sqlCommand1.Parameters["@CategoryID"].Direction = ParameterDirection.Input;
    sqlCommand1.Parameters["@CategoryID"].Value = 1;
    sqlCommand1.Parameters.Add ("@CountProducts", SqlDbType.Int);
    sqlCommand1.Parameters["@CountProducts"].Direction = ParameterDirection.Output;
    sqlCommand1.Parameters["@CountProducts"].Value = null;
    sqlCommand1.ExecuteScalar();
    this.textBox1.Text = sqlCommand1.Parameters["@CountProducts"].Value.ToString();
    sqlConnection1.Close();
}

Carter Wickstrom
wickerman26(at)hotmail(dot)com