Question Extract value from stored procedure that takes a parameter

Ice

Member
Joined
Jan 25, 2013
Messages
14
Programming Experience
Beginner
Hi

I have a stored procedure, which I cannot change (it is used by older programs).
It is passed an int of 15 and then an ID is generated and written to a database table. The created ID is then suppose to be selected and returned.
The INSERT does not seem to be working. I'm not getting the created ID value from my code, I am getting the value I passed to the procedure when I get to this
line of code "sessionID = sessionProcedureID.Value.ToString()";.
Below the stored procedure and my code

C#:
/***************Stored Procedure***********************/
USE [testDataBase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MakeValue]
(
  @timeLive smallint
)
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @CookieID varchar(50)
  SET @CookieID = NEWID()
  INSERT INTO dbo.Sessions (CookieID, TTL,LastVisit) VALUES (@CookieID, @timeLive,CURRENT_TIMESTAMP)

  IF @@ERROR = 0 AND @@ROWCOUNT = 1
  BEGIN
    SELECT @CookieID AS '@@CookieID'
    RETURN 0
  END
  ELSE
  BEGIN
    RAISERROR('Something went wrong :-(', 16, 1)
    RETURN -1
  END
END
C#:
/************************CODE**************************/
int sessionTimeOut = 15;
string sessionID = "";

  if (StdVars.sqlConn1.State == ConnectionState.Closed)
  {
  StdVars.sqlConn1.Open();
  }

  SqlCommand sqlcmd = new SqlCommand();
  sqlcmd.Connection = StdVars.sqlConn1;
  
  IDbDataParameter sessionProcedureID = sqlcmd.CreateParameter();
  sqlcmd.CommandText = "MakeValue";
  sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;
  sessionProcedureID.ParameterName = "@timeLive";
  sessionProcedureID.Value = sessionTimeOut;
  sessionProcedureID.Direction = System.Data.ParameterDirection.Input;
  sessionProcedureID.DbType = System.Data.DbType.String;
  sqlcmd.Parameters.Add(sessionProcedureID);
  sqlcmd.ExecuteNonQuery();

  sessionID = sessionProcedureID.Value.ToString();
  
  if (sessionID.Length > 0) { 
  HttpCookie cookID = new HttpCookie("SessionSettings");
  cookID["ID"] = sessionID;
  }

  if (StdVars.sqlConn1.State == ConnectionState.Open)
  {
  sqlcmd.Dispose();
  StdVars.sqlConn1.Close();
  StdVars.sqlConn1.Close();
  }
 
Last edited:

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,143
Location
Sydney, Australia
Programming Experience
10+
Of course you're getting back the value you pass in. You're getting the Value of that parameter. Where in that sproc is a new value assigned to the @timeLive parameter? Nowhere, so why would you expect to get a new value from that parameter in your C# code?

What is it that you actually want back? The result of a SELECT statement, right? That's a query, right? Therefore, is ExecuteNonQuery the right method to call? What is the method you call when you want the value from the first column of the first row of the result set of a query?
 

Ice

Member
Joined
Jan 25, 2013
Messages
14
Programming Experience
Beginner
Thanks

I changed

C#:
sqlcmd.ExecuteNonQuery();
sessionID = sessionProcedureID.Value.ToString();
to

C#:
sqlcmd.ExecuteScalar();
SqlDataReader reader = sqlcmd.ExecuteReader();

if (reader.Read())
{
   if (reader.HasRows)
                   {
                         sessionID = reader["@@SessionID"].ToString();
                   }
            }
 

Ice

Member
Joined
Jan 25, 2013
Messages
14
Programming Experience
Beginner
Hi

I got the return value to work but is there a reason within my code that my Insert would not fire?
 
Top Bottom