You must Sign In to post a response.
  • Category: SQL Server

    Need detail about stored procedure

    Hi Friends
    I want to write procedure for update the table..basically we write
    update tbname set column_name='"+text1.text+"'. now i want to write these query in procedure and also update the value in front end user input. how its possible. can any one guide me with example
  • #748391
    Hi,
    First Create a strode procedure like this.
    <code>CREATE PROCEDURE MasterUpdateProc
    (
    @Id INTEGER,
    @Name VARCHAR(10),
    @Address VARCHAR(50),
    )

    AS
    BEGIN

    UPDATE employee SET
    Name = @Name, Address = @Address, WHERE id = @Id
    END
    </code>
    Now C# code is:

    string connectionString =
    "server=.;" +
    "initial catalog=employee;" +
    "user id=sa;" +
    "password=sa123";
    using (SqlConnection conn =
    new SqlConnection(connectionString))
    {
    conn.Open();
    using (SqlCommand cmd =
    new SqlCommand())
    {
    cmd.CommandType=CommandType.StoredProcedure;
    cmd.CommandText="MasterUpdateProc";
    cmd.Parameters.AddWithValue("@Id", 1);
    cmd.Parameters.AddWithValue("@Name", text1.text);
    cmd.Parameters.AddWithValue("@Address", text2.text);

    int rows = cmd.ExecuteNonQuery();

    //rows number of record got updated
    }
    }
    Thank you,
    Govind.

  • #748394
    Stored Procedure:-

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[UpdateEmployee]
    @FirstName varchar(50),
    @LastName varchar(50),
    @City varchar(50),
    @Country varchar(50)
    AS
    BEGIN
    SET NOCOUNT ON;
    UPDATE Employees SET City = @City, Country = @Country
    WHERE FirstName=@FirstName AND LastName=@LastName
    END



    C#:-

    String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "UpdateEmployee";
    cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim();
    cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim();
    cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();
    cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim();
    cmd.Connection = con;
    try
    {
    con.Open();
    cmd.ExecuteNonQuery();
    lblMessage.Text = "Record updated successfully";
    }
    catch (Exception ex)
    {
    throw ex;
    }
    finally
    {
    con.Close();
    con.Dispose();
    }

  • #748405
    Hi,

    If you want to know how to prepare stored procedure for update statement, then refer below sample


    CREATE PROCEDURE Update_Details
    (
    @SP_Id INT, -- INPUT PARAMETER
    @error VARCHAR(100)=NULL OUTPUT -- output parameter
    )
    AS
    BEGIN
    UPDATE TABLENAME SET SP_NAME='NNNN'
    WHERE SP_ID=@SP_ID
    END


    If you to know more information about stored procedures how to prepare it then refer below link

    http://www.dotnetspider.com/resources/44915-What-Stored-Procedure-s-What-s-need-use-Stored-Procedures.aspx

    If you want to know more details about how to call stored procedure in your application then refer below link
    http://www.dotnetspider.com/resources/45044-Call-Stored-Procedure-Application.aspx

    Hope this will help you to resolve your issues...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #748421
    To fire a query using stored procedure you need to set the 'SQLCommand.CommandType' to 'CommandType.StoredProcedure' and then pass parameter to stored procedure
    Here is the code to call on Update Button

    using (SqlConnection objCon = new SqlConnection(dc.objCon)) {
    using (SqlCommand objCmd = new SqlCommand("sp_TestProc", objCon)) {
    objCmd.CommandType = CommandType.StoredProcedure;

    objCmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
    objCmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;
    objCmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text;
    objCmd.Parameters.Add("@id", SqlDbType.Number).Value = txtId.Text;

    objCon.Open();
    objCmd.ExecuteNonQuery();
    }
    }


    Here the stored procedure, in which we have update first name, last name and city with the help of id of employee

    CREATE PROCEDURE sp_TestProc AS
    BEGIN
    UPDATE employee SET
    FirstN = @FirstName, LastN = @LastName, city = @City
    WHERE id = @id
    END

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #748422
    Check this sample code.




    CREATE PROCEDURE UpdateTable
    @Id int,
    @LastName nvarchar(50),
    @FirstName nvarchar(50)

    AS

    Update User set FirstName = @FirstName AND LastName = @LastName

    WHERE Id=@ID AND Active IS NOT NULL;
    GO

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #748425
    As shown by govinda rao you can use the stored procedure. Now executenonquery method of sqlcommand object returns the number of rows effected (inserted/updated/deleted) . You can check the number of rows affected and lets say if more than one row is affected then you can update the ui accordingly.

    You can also use out parameter in the stored procedure to return any value from the stored procedure

    Miss. Jain
    Microsoft Certified Technology Specialist in .Net


  • Sign In to post your comments