running the Procedure
-
Hi all, Can anybody help me here ... how to run a procedure with outparameter... please provide the code if u can... Thanks alot
T@SU
using (SqlConnection sc = new SqlConnection())
{
StringBuilder sql = new StringBuilder(); // or simply use string instead
sql.Append("exec sp_YourProc");
string connStr = "...";
sc.ConnectionString = connStr;
sc.Open();
using (SqlCommand sco = new SqlCommand())
{
try
{
sco.CommandType = CommandType.Text;
sco.CommandText = sql.ToString();
sco.Connection = sc;
using (SqlDataReader sr = sco.ExecuteReader())
{
// Do something with the incoming data;
}
}
catch (SqlException exc)
{
// ... Deal with exceptions
}
}SkyWalker
-
Hi all, Can anybody help me here ... how to run a procedure with outparameter... please provide the code if u can... Thanks alot
T@SU
Same as any normal procedure, just make sure you add the output parameter and give it a Direction of Output (or InputOutput)
SqlParameter outParam = new SqlParameter("@myOutParam",SqlDbType.Int); outParam.Direction = ParameterDirection.Output; myCmd.Parameters.Add(outParam); -
using (SqlConnection sc = new SqlConnection())
{
StringBuilder sql = new StringBuilder(); // or simply use string instead
sql.Append("exec sp_YourProc");
string connStr = "...";
sc.ConnectionString = connStr;
sc.Open();
using (SqlCommand sco = new SqlCommand())
{
try
{
sco.CommandType = CommandType.Text;
sco.CommandText = sql.ToString();
sco.Connection = sc;
using (SqlDataReader sr = sco.ExecuteReader())
{
// Do something with the incoming data;
}
}
catch (SqlException exc)
{
// ... Deal with exceptions
}
}SkyWalker
-
use
sco.Parameters.Add()SkyWalker
-
Same as any normal procedure, just make sure you add the output parameter and give it a Direction of Output (or InputOutput)
SqlParameter outParam = new SqlParameter("@myOutParam",SqlDbType.Int); outParam.Direction = ParameterDirection.Output; myCmd.Parameters.Add(outParam);Thanks alot for ur replies.... after executing the procedure ... how to retrive the result.. am trying like this but it dosent work.. dataset abc=cmd.ExecuteNonQuery(); i have done like this..... SqlConnection conn = new SqlConnection(connc); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_abc"; SqlParameter paramPAF = new SqlParameter(); paramPAF.ParameterName = "P_Id"; paramPAF.SqlDbType = SqlDbType.Int; paramPAF.Value = Section_Id; paramPAF.Direction = ParameterDirection.Input; SqlParameter paramNbr = new SqlParameter(); paramNbr.ParameterName = "ResultRS"; paramNbr.SqlDbType = SqlDbType.Variant; paramNbr.Direction = ParameterDirection.Output; cmd.Parameters.Add(P_Id); // parameter 1 cmd.Parameters.Add(sp_abc); cmd.ExecuteNonQuery(); so now how to use third parameter "ResultRS" ? Please help me here..
vikas da
-
Thanks alot for ur replies.... after executing the procedure ... how to retrive the result.. am trying like this but it dosent work.. dataset abc=cmd.ExecuteNonQuery(); i have done like this..... SqlConnection conn = new SqlConnection(connc); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_abc"; SqlParameter paramPAF = new SqlParameter(); paramPAF.ParameterName = "P_Id"; paramPAF.SqlDbType = SqlDbType.Int; paramPAF.Value = Section_Id; paramPAF.Direction = ParameterDirection.Input; SqlParameter paramNbr = new SqlParameter(); paramNbr.ParameterName = "ResultRS"; paramNbr.SqlDbType = SqlDbType.Variant; paramNbr.Direction = ParameterDirection.Output; cmd.Parameters.Add(P_Id); // parameter 1 cmd.Parameters.Add(sp_abc); cmd.ExecuteNonQuery(); so now how to use third parameter "ResultRS" ? Please help me here..
vikas da
You have to use the cmd.ExecuteReader() and not the cmd.ExecuteNonQuery() - the last method return an int of numbers of rows affected, usually used with insert query or update query (also useful with select count...). Try this: SqlDataReader reader = cmd.ExecuteReader(); reader.Close(); string result = reader["ResultRS"].ToString();
-
You have to use the cmd.ExecuteReader() and not the cmd.ExecuteNonQuery() - the last method return an int of numbers of rows affected, usually used with insert query or update query (also useful with select count...). Try this: SqlDataReader reader = cmd.ExecuteReader(); reader.Close(); string result = reader["ResultRS"].ToString();
-
it is showinf error like "Procedure or function has too many arguments specified". i am using "paramNbr.SqlDbType = SqlDbType.Variant;" in case out parameter sqldbtype is that fine..
T@SU
SqlConnection conn = new SqlConnection("CONNECTION HERE"); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_abc"; //Create the first parameter SqlParameter firstParam = new SqlParameter("P_Id", SqlDbType.Int); firstParam.Value = 0; //Place your variable firstParam.Direction = ParameterDirection.Input; cmd.Parameters.Add(firstParam); //Parameter 1 //Create the second parameter SqlParameter secondParam = new SqlParameter("ResultRS", SqlDbType.Variant); secondParam.Direction = ParameterDirection.Output; cmd.Parameters.Add(secondParam); //Parameter 2 int count = cmd.ExecuteNonQuery(); Console.WriteLine(secondParam.Value.ToString()); This is correct now, secondParam.Value and you read the value News at http://support.microsoft.com/kb/308621[^]
modified on Monday, January 07, 2008 8:36:36 AM
