Dept Table
EXAMPLES
Types of stored procedures
CREATE PROCEDURE PRINTTABLE
@N INT
AS
BEGIN
DECLARE @C INT = 1
WHILE @C <= 20
BEGIN
PRINT CAST(@N AS VARCHAR) + 'X' + CAST(@C AS VARCHAR) + ' = ' + CAST(@N * @C AS VARCHAR)
SET @C = @C + 1
END
END
A stored sub program is the programs that are permantely stored in the database. While working with front end application like dotnet, it is recommended to write direct sql statements within the quotes because of the stored sub program the execution will be fast and increases the performance of the application compared to individual sql statements.
Before executing individual sql statements, sql server has to perform parsing, execution plan generator and cost estimation. During parsing it will verify the syntax of the given statement availability of objects in the database. Whether or not the current user has permissions to perform that operation, data type compatibility.
As of these steps take time, execution will become slow that effect the performance of the application.
Sql statement -> parsing (Execution plan generation)
Syntax
Object availability
Security (permission)
Data type (compatibility)
Execution plan with low-cost
Executes the statement.
In case of stored sub programs , while creating the stored sub programs itself parsing execution plan generator and cost estimation will be done and execution plan with low cost for every statement in the stored sub program will be permantely stored in the database. As execution plan is readily available in database, execution will be fast.
Stored sub program are classified into stored procedure, user defined functions, and triggers.
Stored procedure
Are the stored sub program that cannot return a value with return statement and they can return a value with output parameter.
A stored procedure can contain key word return without a value to return. And the purpose of this is to exit from the procedure without completing execution of the procedure.
A procedure is a precompiled named block stored in database that performs a task.
Procedure are created to perform DML operations over database
Query optimizer
It is a sub component in the database system or it is a file in SQL server. Query optimizer will prepare execution strategies for the given sql command or stored procedures.
Execution strategies means different possible way of executing the commands or stored procedure out of all the execution strategies the efficient strategies is called as “Execution Plan” and according to the execution plan the sql command or stored procedure will execute.
Stored procedure is precompiled and execution plan will be prepared and that will be stored in database. So when the stored procedure is executed directly, the execution will be fast.
Syntax:
Create /alter procedure <name>
Parameters
As
Begin
Statement
End
create procedure mysum(@a int, @b int, @c int,@d int, @e int)
as
begin
print @a+@b+@c+@d+@e
end
Main advantage of creating procedures and functions
· Reusability
· Pre compiling
Executing the stored procedure
To execute the stored procedure use the execute command that has the following syntax
exec mysum 10,20,30,40,50
Procedure are two types
Formal
Actual
Parameters which are declared in procedure declaration are called formal parameters
Parameters which are passed at the time of calling procedure are called “Actual Parameters”
Formal and actual parameters are of two types
Input
Output
By default, parameters are input parameters and any changes made to input parameters within the procedure will not reflect in the argument passed to them, which is called as call by value.
When parameters are declared as output parameter than any changes made to those parameters within the procedure will reflect in argument passed to them, which is called as call by Reference.
To declare the parameter as output parameter, while creating the procedure use the keyword output or out in the parameter declaration.
Output parameter always sends value to the main program and it is write only.
Once, the procedure is created, the procedure can be executed from SSMS or .Net application or Java application.
In ssms procedure can be executed in two ways
Positional notation
In positional notation, parameters are mapped through their position
Create procedure addnum (@x int, @y int)
As
Begin
Declare @z int
Set @z=@x+@y
Print @z
End
Execution
addnum 50,30
Named notation
Here, the parameter are mapped through their names
create procedure addnum11(@x int,@y int,@z int output)
as
begin
set @z=@x+@y
end
declare @a int, @b int, @c int
set @a =30
set @b=40
exec addnum11 @a, @b, @c output
print @c
output:
70
Output parameters
When a stored procedure is created with ‘n’ no of parameters than while calling the stored procedure you must pass ‘n’ no of arguments.
When you want to allow the user to call a stored procedure with variable no of arguments and not a fixed no of arguments, then you have to declare the parameters as optional.
To declare a parameter as optional parameter initialize the parameter while creating the procedure . the value to which the parameter is initialized is called as Default Argument.
While calling a stored procedure, it is optional to pass an argument to the optional parameter and when you are not passing an argument to the optional parameter, than it will automatically take default argument and execute the stored procedure.
create procedure proc1
as
begin
print 'my first procedure'
end
exec proc1
output:
my first procedure
create procedure proc2(@x int, @y int)
as begin
declare @z int
set @z=@x+@y
print 'the sum of 2 no'+cast(@z as varchar)
end
exec proc2 100,50
output:
the sum of 2 no150
create procedure proc3(@x int=100, @y int)
as
begin
declare @z int
set @z=@x+@y
print ' the sum of 2 no '+cast(@z as varchar)
end
exec proc3 200,25
exec proc3 @x=200, @y=13
exec proc3 @x=Default, @y =25
exec proc3 @x=Default, @y =25
a procedure with output parameter
create procedure proc4(@x int,@y int, @z int output)
as
begin
set @z=@x+@y
end
declare @a int
exec proc4 500,250,@a output
print @a
The following example creates the stored procedure to allow the user to call it either with two or three or four or five arguments.
create procedure proc7(@a int, @b int,@c int=0,@d int =0, @e int=0)
as
begin
return @a+@b+@c+@d+@e
end
create procedure proc8 (@x int, @y int=20,@z int output)
as
begin
set @z=@x+@y
end
declare @k int
exec proc8 10,default,@k output
print @k
output:
30
Create a procedure to insert a row into dept table
create procedureinsert_dept
@dno int,@deptname varchar(30),@location varchar(50)
as
begin
insert into dept values(@dno,@deptname,@location)
end
exec insert_dept 50,'purchase','delhi'
Create a procedure to delete a row from the table dept
create proceduredelete_dept
@dno int
as
begin
delete dept wheredeptno=@dno
end
exec delete_dept 50
create procedureupdate_dept
@dno int,@dn varchar(20),
@location varchar(15)
as
begin
update dept set dname=@dn,loc=@location wheredeptno=@dno
end
create a procedure to insert a value into emp table
create procedureinsert_emp(@empno int, @ename varchar(50),@sal money,@deptno int)
as
begin
insert into emp(empno,ename,sal,deptno)values(@empno,@ename,@sal,@deptno)
end
exec insert_emp 1016,'sudhir',2500,10
create a procedure to insert values into emp table but with validations
empno-not null
empno-cannot be duplicated
salary cannot be less than 2500
deptno should be present in dept table
create procedureinsert_emp1(@empno int, @ename varchar(50),@sal money,@deptno int)
as
begin
if @empno isnull
begin
print 'empno cannot be null'
return
end
if exists(select * from emp where empno=@empno)
begin
print 'empno cannot be duplicated'
return
end
if @sal <2500
begin
print 'salary cannot be less than 2500'
return
end
if not exists(select * from dept wheredeptno=@deptno)
begin
print ' deptno not found in dept table'
return
end
insert into emp(empno,ename,sal,deptno)values(@empno,@ename,@sal,@deptno)
end
exec insert_emp1 Null,'sudhir',2500,10
empno cannot be null
exec insert_emp1 1016,'sudhir',2500,10
empno cannot be duplicated
exec insert_emp1 1013,'sudhir',2400,10
salary cannot be less than 2500
exec insert_emp1 1013,'sudhir',2800,70
deptno not found in dept table
create procedurededucation(@empno int,@pf money output, @pt money output)
as
begin
declare @sal money
select @sal=SAL FROM EMP WHERE EMPNO=@empno
set @pf=@sal*0.12
set @pt=@sal*0.05
end
create a procedure which takes the empno and returns the pf and pt at 12% and 5% respectively
create procedurededucation(@empno int,@pf money output, @pt money output)
as
begin
declare @sal money
select @sal=SAL FROM EMP WHERE EMPNO=@empno
set @pf=@sal*0.12
set @pt=@sal*0.05
end
declare @vpf money,@vpt money
exec deducations 1014,@vpf output,@vpt output
print @vpf
print @vpt
output
300.00
125.00
Double parameters
alter procedurenet_Sal(@empno int)
as
begin
declare @vsal money,@nsal money,@vpf money,@vpt money,@sal int
exec deducations @empno,@vpf output,@vpt output
select @sal=SAL from EMP where EMPNO=@empno
print 'net sal' +Cast(@sal as varchar)
end
exec net_Sal 1014
A procedure which will insert values intot the dept table by generating a unique deptno
create procedureinsert_deptunique(@dname varchar(50),@loc varchar(50))
as
begin
declare @deptno int
select @deptno=ISNULL(max(deptno),0)+10 from DEPT
insert into DEPT values(@deptno,@dname,@loc)
end
execinsert_deptunique 'research','hyderabad'
To delete a a stored procedure
Drop procedure procedurename
The following example deletes the procedure mysum
Delete proc mysum
Permissions
To allow other users to execute your procedure you have to grant execute permission on the procedure
The following example grants execute permission on the procedure mysum to the user “ssauser”
Grant execute on mysum to ssauser
Types of stored procedures
System(sp_)
These procedure will start with sp_
These are used to perform user and administrative task
Sp_help
Sp_rename
Sp_helpconstraint
Sp_renameconstraint
Temporary Stored Procedure
This procedure will start with “#”. This will be stored in the memory temporary when the sql server or management studio is closed than this procedure will be deleted.
User defined stored procedure
Create procedure <procedure name>
[With recompile]
As
Begin
Statement
End
To get list of stored procedure
To get list of stored procedure use any of the following two select statements
Select * from sysobjects where type=’p’
Select * from sys.procedures
Getting Definition of a Procedure
To get the definition of a stored procedure use the built in stored procedure
“sp_helptext” with procedure name as argument.
Sp_helptext ‘procname’
Sp_helptext ‘mysum’
For, not displaying the definition of stored procedure to anyone, use encryption option while creating the stored procedure
Drop procedure procedurename
The following example deletes the procedure mysum
Delete proc mysum
Permissions
To allow other users to execute your procedure you have to grant execute permission on the procedure
The following example grants execute permission on the procedure mysum to the user “ssauser”
Grant execute on mysum to ssausersCREATE PROCEDURE PRINTTABLE
@N INT
AS
BEGIN
DECLARE @C INT = 1
WHILE @C <= 20
BEGIN
PRINT CAST(@N AS VARCHAR) + 'X' + CAST(@C AS VARCHAR) + ' = ' + CAST(@N * @C AS VARCHAR)
SET @C = @C + 1
END
END
