How to use Function and stored procedure in sql

What is stored procedure?

A stored procedure is a compiled query which stores in the database. In other words, we can say it is a database object which stores in the database.

Create Procedure For Select:

create proc sp_select as select * from employee
exec sp_select

Create a procedure for insert:

create proc sp_insert @id int,@name varchar(50), @mobile varchar(50) 
as 
insert into employee values(@id,@name,@mobile)
exec sp_insert 1,'Rashid','9358156172'
exec sp_insert 2,'Raja','9998154545'
exec sp_insert 3,'Priya','8888154545'

Result:

Create Procedure for Update:

create proc sp_update
@id int,@name varchar(50)
as
begin
update employee set name=@name where id=@id
end
exec sp_update 1,'Deepak'
exec sp_select

Result:

 

How to use multiple queries in single stored procedure:

create proc sp_all @op varchar(50)='',@Id varchar(50)='',@Name varchar(50)='',@Address varchar(50)='', @Mobile varchar(50)=''
as
begin
if @op='insert'
begin
insert into employee_info values(@id,@Name,@Address,@mobile)
end
if @op='update'
begin
update employee_info set name=@name,Address=@Address where Id=@Id
end
if @op='delete'
begin
delete from employee_info where id=@id
end
if @op='select'
begin
select * from employee_info where id=@id
end
else
begin
select * from employee_info
end
end

Insert:

exec sp_all 'insert', '1','Sam','Noida','7879456236'
exec sp_all 'insert', '2','Dam','Delhi','9540155984'
exec sp_all 'insert', '3','Ram','Noida','9899968025'
exec sp_all 'insert', '4','Raman','Meerut','7845129632'
exec sp_all 'insert', '5','Vons','Mumbai','9997854545'
exec sp_all

Result:

Update:

exec sp_all 'update','1','Queen','Agra'
exec sp_all

Result:

procedure

Delete:

exec sp_all 'delete', '5'
exec sp_all

Result:

Advantage Of Stored Procedure:

  • Better performance
  • Less traffic
  • Security
  • Integrity
  • Code reusability

 

How to create a function in SQL:

create function odd_even(@no int) returns varchar(50)
as
begin
declare @result varchar(50)
if @no%2=0
set @result='no is even'
else
set @result='no is odd'
return @result
end

select dbo.odd_even(5)
select dbo.odd_even(10)

Result:

no is odd
no is even

Create function for Sum Two Number:

create function sum(@no1 int,@no2 int) returns int
as
begin
declare @result int
set @result=@no1+@no2
return @result
end

Result:

Difference Between Function And Store Procedure:

  • The function must return a value type.
  • The function will permit solely select statement it will not permit to use DML statements.
  • It will permit solely input parameter and doesn’t support output parameter.
  • It will not permit to use try-catch blocks.
  • Transaction is not allowed within function
  • The function will be referred to as from a select statement.

 

  • A procedure might or not return type.
  • Can have a select statement similarly as DML statements like insert, delete, update.
  • It will have each input-output parameters.
  • we can use try-catch blocks.
  • we can use transaction within the procedure.
  • The procedure can’t be called from select/where/having etc statement, executes statement is wont to call procedure.

Use Of Concat() Function:

If we would like to concatenate 2 or over 2 column in a single column than we tend to use concatenate function.

We have a table employee we would like to concatenate Id, name, department.

select concat(id,' ', Emp_name,' ', department) from employee

Result:

Use Of Coalesce Function:

If we’ve got a table of name info such their columns have a Name, Office number, personal number, home number the data within the table is like that some workers have all the three number office, personal and home some have a pair of numbers like personal, home and a few have just one number like home.Now we might wish to realize info concerning all such workers if an associate worker has all the three number then choose office number if an associate worker has a pair of number personal and home then choose the personal number and if a worker has only one number that is home then choose a home number.

create table info(Name varchar(50),Office_no bigint,personal_no bigint,Home_no bigint)

insert into info(name,Home_no) values('Ajay',9245787874);
insert into info(name,Office_no) values('Sonu',4565325898);
insert into info(name,personal_no) values('priya',9887457878);
insert into info(name,Office_no,Home_no) values('Ameeq',7889662521,8545787896);
insert into info(name,personal_no,Home_no) values('Rashid',7889662521,8545787896);
insert into info(name,Office_no,personal_no) values('Tiwari',7889662521,8545787896);
insert into info(name,Office_no,personal_no,Home_no) values('Vons',7889662521,8545787896,8523124578);

select name,coalesce(office_no,personal_no,Home_no)as Number from info

Result:

Limitation Of Coalesce Function:

The data form of the column that we wish to pick with coalesce function should be of integer data kind.

Use Of IsNull:

If we have got a 2 table Table1 and Table2 and their columns are as follows Name, project_Id, and Project_name, project_Id severally. we would like to pick values from each the table such the null value fills with ‘Not Assign’.

Create table Table1(Name varchar(50),project_id int)
Create table Table2(Project_Name varchar(50),project_id int)
insert into Table1 values('Ramu',2)
insert into Table1 values('Sonam',1)
insert into Table1 values('Amit',1)
insert into Table1 values('Sam',4)

insert into Table2 values('Wcf',1)
insert into Table2 values('Mvc',2)
insert into Table2 values('C#',3)
insert into Table2 values('Ajax',4)
insert into Table2 values('Php',5)

select aa.project_id,aa.project_name,bb.name as Name 
from Table2 as aa left join Table1 as bb on aa.project_id=bb.project_id


Result:

select aa.project_id,aa.project_name,isnull(bb.name,'Not Assign')as Name 
from Table2 as aa left join Table1 as bb on aa.project_id=bb.project_id

Result:

We have additionally achieved this result by the employment of the coalesce function.

select aa.project_id,aa.project_name,coalesce(bb.name,'Not Assign')as Name 
from Table2 as aa left join Table1 as bb on aa.project_id=bb.project_id

Result:

Use Of Exists:

Exists could be a boolean operator employed in a subquery to check whether or not the inner question returns any row. If it does, then the outer question payoff. If not, the outer question doesn’t execute, and also the entire SQL statement returns nothing.

Syntax:

Select"column_name1"
From"table_name1"
Where EXISTS 
(Select * 
From"table_name2"
Where"condition");

We have created two table which is as follows.

create table Brand_info(Outlet_name varchar(50), sales int,Date varchar(50))
create table Location(Address varchar(50), Outlet_name varchar(50))

insert into Brand_info values('Puma',1000,'Jan-02-2018')
insert into Brand_info values('Lee',200,'Jan-04-2018')
insert into Brand_info values('Reebok',800,'Jan-06-2018')
insert into Brand_info values('Louis Philippe',1250,'Feb-07-2018')

insert into Location values('Delhi','Louis Philippe')
insert into Location values('Noida','Puma')
insert into Location values('Agra','Reebok')
insert into Location values('Pune','Lee')

Select SUM(Sales) From Brand_info
Where EXISTS
(Select * From Location
Where address = 'pune');

Result:

Select * From Brand_info
Where EXISTS
(Select * From Location
Where address = 'Agra');

Result:

Use Of Intersect:

The INTERSECT command in SQL combines the results of 2 SQL statement and returns solely information that is available in each SQL statements.

INTERSECT is thought of as Associate in AND operator (value is chosen provided that it seems in each statement), whereas UNION and UNION ALL are thought of as an OR operator (value is chosen if it seems in either the primary or the second statement).

Syntax:

[SQL Statement 1]
INTERSECT
[SQL Statement 2];

We have created two table which is as follows.

create table Brand_info(Outlet_name varchar(50), sales int,Date varchar(50))
create table Online(Outlet_name varchar(50),date varchar(50), sales varchar(50))

select outlet_name from Brand_info
intersect
select outlet_name from Online

Result:

Note: The intersect command can solely come distinct values.

Conclusion:

This article delineates a way to use varied operator and command in SQL server. knowing a way to increase performance, From optimizing queries like procedure, function, keyword there square measure many ways to extend performance. If you are associate SQL developer wanting, learning and implementing these article will facilitate to develop SQL queries.

Follow me!
Latest posts by Imran Saifi (see all)

Leave a Comment