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

    Update command in SQL Stored procedure

    Hi,

    I want to pass the parameter to Sql stored procedure to update the data. But i could not do this. could you please help me.

    create proc std_data_updata
    @Std_no varchar(10), @Std_name varchar(10), @Std_class varchar(10), @Std_city varchar(10),@Std_phone varchar(10), @Std_age int
    as
    update std_table set Std_no= @Std_no, Std_name=@Std_name, Std_class=@Std_class, Std_city=@Std_city, Std_phone=@Std_phone, Std_age=@Std_age where Std_name=@Std_name

    how to pass the values. Stored procedure is executed successfully. But could not pass the value.

    exec std_data_updata @Std_no='011', @Std_name='gopadfa',@Std_class='asp.net',@Std_city = 'aga', @Std_phone = '98745', @Std_age = 30 where @std_no = 001
  • #764984
    Hi

    what is your issue?

    Can you explain more.

    then

    your stored procedure this



    update std_table set Std_no= @Std_no, Std_name=@Std_name, Std_class=@Std_class, Std_city=@Std_city, Std_phone=@Std_phone, Std_age=@Std_age where Std_name=@Std_name



    in this query why you use set and where same field?

    set Std_name=@Std_name

    where Std_name=@Std_name

    then

    your execute query

    exec std_data_updata @Std_no='011', @Std_name='gopadfa',@Std_class='asp.net',@Std_city = 'aga', @Std_phone = '98745', @Std_age = 30 where @std_no = 001

    where applying std_no

    different in your execute and stored procedure.

    what is issue explain clearly and more.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #764985
    Hi

    I just want update command in stored procedure and how to pass the value to update the data... if my above code is wrong means can you explain the stored procedure for updated command with minimum 3 fields.

    -----------------------------------------------------------------------------
    Regards,
    Gopi A.
    +91 9894315571
    Skype:gopi.net
    http://asaigopi-dotnet.blogspot.in/

  • #764990
    The code seems to be fine. Can you check whether the value is there in the table

    select * from std_table where Std_name='gopadfa'

    Some times spaces may be there. You can try "Like" in the where class also.

    By Nathan
    Direction is important than speed

  • #764992
    Hi

    I change your update procedure right format follow the below step

    step1
    ===========
    Create and Inserted the Table


    CREATE TABLE STD_TABLE
    (
    Std_no varchar(10),
    Std_name varchar(10),
    Std_class varchar(10),
    Std_city varchar(10),
    Std_phone varchar(10),
    Std_age int
    )

    insert into STD_TABLE VALUES('1001','AA1','BB1','CC1','123456',22)
    insert into STD_TABLE VALUES('1002','AA2','BB2','CC2','123457',23)


    Step2
    =====
    Create the Procedure



    CREATE PROC STD_DATA_UPDATA
    @Std_no varchar(10), @Std_name varchar(10), @Std_class varchar(10), @Std_city varchar(10),@Std_phone varchar(10), @Std_age int
    as
    update std_table set Std_name=@Std_name, Std_class=@Std_class, Std_city=@Std_city, Std_phone=@Std_phone, Std_age=@Std_age where Std_no=@Std_no



    3.Execute the Procedure


    EXEC STD_DATA_UPDATA @STD_NO='1001', @STD_NAME='GOPADFA',@STD_CLASS='ASP.NET',@STD_CITY = 'AGA', @STD_PHONE = '98745', @STD_AGE = 30


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #764999
    it is simple, Just like you have the ability to use parameters with your SQL code you can also setup your stored procedures to accept one or more parameter values
    see below simple procedure
    CREATE PROCEDURE uspGetAddress @City nvarchar(30)
    AS
    SELECT *
    FROM AdventureWorks.Person.Address
    WHERE City = @City

    Now to call it, I will use
    EXEC uspGetAddress @City = 'testCity'
    Hope, it will clear your doubts

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

  • #765000
    Hi,

    Thanks for your help. Now i understood and solved my issue.

    -----------------------------------------------------------------------------
    Regards,
    Gopi A.
    +91 9894315571
    Skype:gopi.net
    http://asaigopi-dotnet.blogspot.in/

  • #765009
    Hi,

    Simply pass the input parameters from your code behind to your stored procedure.

    Ex:

    protected btnUpdate_Click(object sender, EventArgs e)
    {
    try
    {
    con.Open();
    cmd = new SqlCommand("Update_Employee_Details", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@EMPNO", value1);
    cmd.Parameters.AddWithValue("@Ename", value2);
    cmd.Parameters.AddWithValue("@Job", Job.Text);

    cmd.ExecuteNonQuery();

    }
    catch (Exception ex)
    {
    }
    finally
    {
    con.Close();
    con.Dispose();
    }
    }


    If you want to know more details about how to work with stored procedure and how to pass parameters to that, I suggest you to refer below link
    "dotnetspider.com/resources/44915-What-Stored-Procedure-s-What-s-need-use-Stored-Procedures.aspx"

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

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


Sign In to post your comments