A stored procedure is a subroutine available to applications that access a relational database management system (RDMS). Such procedures are stored in the database data dictionary.
Suppose you have to update user's login time in database when user logged in. Then we need to create a stored procedure that accept logid of currently logged in user and update their login time. I am writting an example to create parametarized stored procedure to update login time.
Example:
Create PROCEDURE [dbo].[usp_UpdateLogInTime] @Logid nvarchar(100), @InTime datetime as SET NOCOUNT ON; update tblLogin set time_logged_in=@InTime where Logid=@LogId
Now you can use it in c# like this:
public void setLogInTime(Int32 logID) { SqlCommand cmd = new SqlCommand("usp_UpdateLoginTime", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Logid", SqlDbType.BigInt).Value = logID; cmd.Parameters.Add("@InTime", SqlDbType.DateTime).Value = DateTime.Now; try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { if (cmd.Connection.State == ConnectionState.Open) cmd.Connection.Close(); cmd.Dispose(); } }
Hope this helps to anybody who start learning sql server
