Home > Forum > ASP.NET > Stored Procedure

Stored Procedure

by Rameshkumar   on Oct 25, 2013   Category: ASP.NET  |  Views: 1397    |  Points: 5   |  Starter 
  Reply
How to create stored procedure and using insert,update and delete command in sql query(or) sql database

Bookmark and Share:
 
  User Reply  | Ask a question  |   Reply 
  Re :Stored Procedure   
by Thamil
on Oct 27 2013 12:19AM
Points : 10
Gold 
Hi Rameshkumar,

Below are the scripts and procedure for CRUD operations.
1. Sample Customer table
CREATE TABLE [dbo].[tblCustomer](
[CustID] [int] IDENTITY(1,1) NOT NULL,
[CustName] [varchar](100) NOT NULL,
[Custaddress1] [varchar](100) NOT NULL,
[Custaddress2] [varchar](100) NOT NULL,
[CustPhone] [varchar](100) NOT NULL,
[CustEmail] [varchar](100) NOT NULL,
[Createdby] [varchar](100) NOT NULL,
[CreatedDt] [datetime] NOT NULL,
[Updatedby] [varchar](100) NULL,
[UpdatedDt] [datetime] NULL,
[Active] [bit] NOT NULL
) ON [PRIMARY]

2. Insert script for customer table
INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])
VALUES('Muthu','Delhi','Maujpur, India','2345678912','muthu99@yahoo.com','admin','Jun 21 2013 2:44:06:420PM',NULL,NULL,1);
INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])
VALUES('Ramesh','Vijayapura','bangalore, India','245352454','rrramesh@gmil.com','admin','Jun 21 2013 2:46:12:750PM',NULL,NULL,1);
INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])
VALUES('Sridhar','Ashok nagar','Chennai, India','45656423422','rrramesh@gmil.com','admin','Jun 21 2013 2:46:49:807PM',NULL,NULL,1);
INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])
VALUES('Kumar','Thrissur','Kochi, India','23456432','rkkum@lmil.com','admin','Jun 21 2013 2:47:56:803PM',NULL,NULL,1);

3. stored proc for insert, update delete and select
CREATE PROC [dbo].[SP_Customer]        
(
@pvchCustId int =0,
@pvchCustName varchar(100)=null,
@pvchustaddress1 varchar(100)=null,
@pvchustaddress2 varchar(100)=null,
@pvchCustPhone varchar(100)=null,
@pvchCustEmail varchar(100)=null,
@pvchCreatedBy varchar(100)=null,
@pvchAction varchar(50)=null,
@pIntErrDescOut int output
)
AS
BEGIN

if(@pvchAction='select')
begin
SELECT CUSTID,CUSTNAME,CUSTNAME,CUSTADDRESS1,CUSTADDRESS2,CUSTPHONE,
CUSTEMAIL,ACTIVE FROM tblCustomer WHERE Active=1;
end
else if(@pvchAction='insert')
begin
INSERT INTO tblCustomer(CustName,Custaddress1,Custaddress2,CustPhone,
CustEmail,Createdby,CreatedDt,Active)VALUES(@pvchCustName,@pvchustaddress1,@pvchustaddress2,@pvchCustPhone,
@pvchCustEmail,@pvchCreatedBy,GETDATE(),1);

end
else if(@pvchAction='update')
begin
UPDATE tblCustomer SET CustName=@pvchCustName,Custaddress1=@pvchustaddress1,Custaddress2=@pvchustaddress2,
CustPhone=@pvchCustPhone,CustEmail=@pvchCustEmail,Updatedby=@pvchCreatedBy,UpdatedDt=GETDATE()
WHERE CustID=@pvchCustId;
end
else if(@pvchAction='delete')
begin
UPDATE tblCustomer SET Active=0,Updatedby=@pvchCreatedBy,UpdatedDt=GETDATE()
WHERE CustID=@pvchCustId;
end

IF (@@ERROR <> 0)
BEGIN
SET @pIntErrDescOut = 1
END
ELSE
BEGIN
SET @pIntErrDescOut = 0
END

END


Please refer the below article how to do insert, update , delete and select in asp.net grdview control with sample code

http://www.dotnetgallery.com/kb/resource10-How-to-perform-insert-update-delete-and-select-rows-in-ASPNET-gridview-c.aspx

Note: Do not create separate thread for same question, you can use reply option in the same thread and provide the necessary details if required instead opnening new thread.

I hope this will help you. thank you.

Recent Post

Latest Posts