Tuesday 2 April 2013

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'


No comments:

Post a Comment