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

Nilanka Dharmadasa · Answer

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.

Nilanka Dharmadasa · Answer

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_iduser_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();

kiran kumar · Answer

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

kiran kumar · Answer

hi niki i am using sqlserver

kiran kumar · Answer

hi niki i am using sqlserver

Nilanka Dharmadasa · Answer

Hi Kiran What's your database? SQL Server? Oracle ? Or something else? Mention that. Ill help you.