stored procedures

Question

i want to retrieve values from database using stored procedure,compare those values to the parameter supplied in the query string...if the value retrieved from the database using stored procedure  equals the parameter supplied in the query string an updation should take place and reflect that changes to my database.......
i have 2 columns named activated and user id in my database,using stored procedure user id should be retrieved and compare that value with parameter supplied in query string and if the comparison is successful an updation of column "activated" should be done......... how the whole thing gets done using stored procedure

Answer 1

Hi kiran,

According to my stroed procedure,

- user id is passed as a parameter.

-If that userid exists, activated column is updated to 1 and return 1. (If you want, you can pass the value for activated column also as a parameter.)

-If userid does not exist, it returns -1.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [mysp]
           
           @user_id    int

AS
BEGIN

 DECLARE @l_count                         INT 
         
SET  @l_count =  (SELECT COUNT (user_id_column_name)                  
                     FROM tabelname
                    WHERE (user_id_column_name = @user_id))       

if(@l_count)>0

Begin

 UPDATE tabelname
   SET
      activated = 1
 WHERE user_id_column_name = @user_id
return 1;
End

return -1;

GO

If you find my answer useful, please tick 'Do you like this answer' checkbox.

Answer 2

Hi Kiran,


Here is the code.



int user_id = 145;//This is the user_id

SqlConnection sqlConn = new SqlConnection("your connectionstring here");               

sqlConn.Open();
SqlCommand sqlCommand = new SqlCommand("sp_name", sqlConn);
sqlCommand.CommandType=CommandType.StoredProcedure;

SqlParameter sp1 = new SqlParameter("@result", 0);
sp1.Direction=ParameterDirection.Output;
sqlCommand.Parameters.Add(sp1);
sqlCommand.Parameters.Add(new SqlParameter("@user_id",user_id));

sqlCommand.CommandTimeout = 0;
sqlCommand.ExecuteNonQuery();

int result = Int32.Parse(sp1.Value.ToString());

if(result == 1)
{
MessageBox.Show("Successful");
}
else
MessageBox.Show("Error");

sqlConn.Close();

Answer 3

hi niki 
  your answer is appreciated, you are very helpful to me........
so i ll try execute this code now....one more doubt regarding my c# program.....how should i pass parameters from my program....just give any rough idea or if there is any article with you please post here
                         thank you my friend

Answer 4

hi niki,
       i am using sqlserver

Answer 5

hi niki,
       i am using sqlserver

Answer 6

Hi Kiran

What's your database?

SQL Server? Oracle ? Or something else?

Mention that. Ill help you.