Definition of stored procedure.
In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).
Create a stored procedure.
create procedure myprocedure
as
begin
(query)
end
Multiple query in a single procedure.
GO
/****** Object: StoredProcedure [dbo].[myprocedure] Script Date: 01/15/2016 09:30:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[myprocedure]
@function nvarchar(50)=null,
@Emailid nvarchar(50)=null,
@sreno int= 0,
@name nvarchar(50)=null,
@password nvarchar(50)=null,
@status nvarchar(50)=null,
@activation nvarchar(50)=null
as
begin
if @function='insert'
begin
insert into dbo.gridview(name,Emailid,password,status,activation)values (@name,@Emailid,@password,@status,@activation)
end
if @function='select'
begin
select *from dbo.gridview
end
if @function='selecttrue'
begin
select *from dbo.gridview where activation='true'
end
if @function='selectfalse'
begin
select *from dbo.gridview where activation='false'
end
if @function='update'
begin
update dbo.gridview set name=@name,Emailid=@Emailid, password =@password,status=@status,activation=@activation
end
if @function='delete'
begin
delete from dbo.gridview where Emailid=@Emailid
end
if @function='search'
begin
select * from dbo.gridview where Emailid=@Emailid and password =@password
end
if @function='update1'
begin
update dbo.gridview set activation='true' where Emailid=@Emailid
end
end
No comments:
Post a Comment