Jesoba.com is launched, Click below image to access it

Jesoba.com is launched, Click below image to access it
Technology and Entertainment Blog

How to create Store Procedure in SQL Server to Insert Data with Perameter

Title : How to create Store Procedure in SQL Server to Insert Data with Perameter.

Article ID : SS0001

Description : This Query will help you to Understand  Create Procedure
                     in SQL Server for Insert Data using Perameter.

                     First Create Table in Database
---------------------------------------------------------
USE EMPLOYEE
create table emp
(


 Idemp BIGINT Constraint pk Primary Key ,
 EmpName VARCHAR(8),
 EmpContact VARCHAR(10)
)

---------------------------------------------------------


Now Create procedure using given SQL Query.

-----------Create Procedure------------------------

USE EMPLOYEE --Database Name in which Procedure Execute. 

CREATE PROCEDURE usp_save_Employee
              @IdEmp AS BIGINT = NULL,
              @EmpName AS VARCHAR(80) = NULL,
              @EmpContact AS VARCHAR(80) = NULL
AS
       
   BEGIN
          INSERT INTO emp                     

                      (
                      IdEmp,   EmpName,  EmpContact
                      )
                      Values
                      (
                      @IdEmp,  @EmpName, @EmpContact
                     
                      )             
   END
-------------------------------------------------------


Call procedure and pass perameter using following code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

public partial class Default3 : System.Web.UI.Page
{
    string  sSqlStr;
    SqlConnection con;
    protected void Page_Load(object sender, EventArgs e)
    {
       
    }
   protected void btnSave_Click(object sender, EventArgs e)
    {
        con = new SqlConnection(@"Data Source=.\SQL2005; Initial Catalog=VSPLHR2011; User ID = sa; Password = n@123");
        con.Open();
        sSqlStr = "usp_save_Employee   @IdEmp = " + Convert.ToInt16(txtid.Text)
                                                    + ", @EmpName     = " + txtempname.Text
                                                    + ", @EmpContac     = " + txtcontact.Text
        SqlCommand cmd = new SqlCommand(sSqlStr , con);
        cmd.ExecuteNonQuery();
        con.Close();
   }
}


Image/Video :Enter data in  TextBoxes and click on save this will save data in
                      database using procedure.
                      Click on Image :
                                                 

Keyword : SQL Server, Store Procedure, Insert Data Using Perameter.

Reference : N/A

No comments:

Google+ Badge

About Author

My photo

I am Senior Server Expert and Professional Blogger.