SQL Procedure example code

------------- CREATE PROCEDURE EXAMPEL ---------------------

create proc sp_CRUD_AdminTable

(

@id int = null,

@name varchar(50)=null,

@lname varchar(50)=null,

@phone bigint =null,

@maildata text =  null,

@emailid varchar(50)=null,

@add varchar(100)=null,

@city varchar(50)=null,

@operation varchar(50)

)

as

begin

set nocount on

if @operation = 'insert'

begin

insert into ADMIN_TABLE (ADM_NAME,ADM_LAST_NAME,ADM_PHONE,ADM_MAIL_DATA,ADM_EMAILID,

ADM_CREATED_DATE, ADM_ADDRESS,ADM_CITY) values (@name,@lname,@phone,@maildata,@emailid,GETDATE(),@add,@city)

end

else if @operation = 'update'

begin

update ADMIN_TABLE set ADM_NAME = @name,ADM_LAST_NAME =@lname,ADM_PHONE=@phone,ADM_MAIL_DATA=@maildata, ADM_EMAILID=@emailid, ADM_ADDRESS=@add,ADM_CITY=@city where ADM_ID = @id

end

else if @operation = 'delete'

begin

delete from ADMIN_TABLE where ADM_ID = @id

end

else if @operation = 'select'

begin

select * from ADMIN_TABLE where ADM_ID = @id

end

else if @operation = 'selectall'

BEGIN

select * from ADMIN_TABLE 

END

end

------------- USE ALL OPERATION OF THIS PROCEDURE---------------------

select * from ADMIN_TABLE

sp_CRUD_AdminTable null,'ramsis', 'code', 123456789, 'hello world','rh@gmail.com','noida','noida', 'insert'

sp_CRUD_AdminTable 1,'aman', 'kumar', 9632587412, 'hello world','aman@gmail.com','noida','noida', 'update'

sp_CRUD_AdminTable @operation= 'selectall'

sp_CRUD_AdminTable @operation= 'select', @id = 2

sp_CRUD_AdminTable @operation= 'delete', @id = 0

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

------------------- Create A simple Procedure ------------------

create procedure sp_AmanTest(

@name varchar(50)=null,

@lname varchar(50)=null,

@emailid varchar(50)=null

)

as 

begin 

set nocount on

insert into ADMIN_TABLE (ADM_NAME,ADM_LAST_NAME,ADM_EMAILID) values (@name,@lname,@emailid);

end

sp_AmanTest @name ='Aman', @lname ='kumar', @emailid='aman@gmail.com'