SQL Pre-defined Functions & Procedures

select * from employ

-- Rank Function
select empid,fname,salary,deptid, RANK () over (order by salary desc) "rank" from employ
select empid,fname,salary,deptid,RANK() over(partition by deptid order by salary desc)"rank" from employ
select empid,fname,salary,deptid,Dense_RANK()over(order by salary desc)"Dense rank" from employ

-- Row nuber()
select empid,fname,salary,deptid,Row_number ()over(order by salary desc)"row numbers" from employ
select empid,fname,salary,deptid,Row_number()over(partition by deptid order by salary desc)"row number"
from employ

-- Type casting functions
select CAST('12' as smallint)

-- old version function
select CONVERT (smallint,'12')

-- new version
select GETDATE(),CONVERT(char(10),getdate(),104),CONVERT(char(12),getdate(),113)

-- user defined datatypes
sp_addtype mailaddress1,'varchar(20)'
create table e1(eid char(4),eaddress mailaddress1)
sp_help e1

drop table e1
sp_droptype mailaddress1

--latest version command for creatig user defined datatype
create type mailaddress2 from varchar(20)create table e2(eid char(4),eaddress mailaddress2)

-- correlated sub queries
select empid,fname,lname,deptid from employ e where exists (select * from department d where e.deptid=d.deptid)

--bulk insert
create table xmlbulkinsert(names xml)
insert xmlbulkinsert(names)
select *from OPENROWSET(bulk 'c:\data.xml',single_blob) as x
select * from xmlbulkinsert

-- index
create table empdemo(eid int,ename char(4))
insert into empdemo values(9,'a')
insert into empdemo values(3,'n')
insert into empdemo values(7,'f')
insert into empdemo values(5,'g')
select * from empdemo

create clustered index idxempdemo on empdemo(eid)
insert into empdemo values(1,'h')

create table empdemo6(empid int,ename char(4))
insert into empdemo6 values(9,'a')
insert into empdemo6 values(3,'n')
insert into empdemo6 values(7,'f')
insert into empdemo6 values(5,'g')
select * from empdemo6

create unique clustered index empidx on empdemo6(empid)
drop index idxempdemo on empdemo
drop table empdemo6
insert into empdemo6 values(5,'g')

create table empdemo7(empid int primary key,ename char(4))
insert into empdemo7 values(9,'a')
insert into empdemo7 values(3,'n')
insert into empdemo7 values(7,'f')
insert into empdemo7 values(5,'g')
select * from empdemo7
create index idxempo on empdemo7(empid)
insert into empdemo7 values(2,null)

create unique clustered index idxemployemp on employ(empid)
create index idxdepartment on department(depthead)
select * from department
select * from employ
create index idxempdept on employ(deptid)

--control flow demo
declare
begin
select @sal int @sal=salary from employ where empid='E06'
if(@sal>25000)
begin
print 'greater than 25000'
print 'Hello world'
end
else
print 'less than or equal to 25000'
end
print @sal

--cases
select fname,deptid,salary, bonus=
case deptid
when 'D01' then salary+salary*.20
when 'D02' then salary
when 'D03' then salary*.50
else
0
end
from employ

--while
select * from employ
select AVG(salary) from employ
select MAX(salary) from employ
while (select AVG(salary) from employ)<65000
begin
select AVG(salary) from employ
update employ set salary=salary*1.5
select AVG(salary) from employ
select MAX(salary) from employ
if(select MAX(salary) from employ)>75000
begin
print 'hello world'
break
end
else continue
end
delete employ
insert employ
select * from cemploy
begin
wait for delay '00:00:05'
select * from employ where deptid='D01'
endbegin
wait for time '12:26:55'
select * from employ where deptid='D01'
end
declare
set @a int @a=20
print @a
declare
select @b int @b=20
print @b
declare @c int@c=20
print @c
select * from employ
drop procedure prdemploy1
create procedure prdemploy1
as
begin
select empid,fname,lname,salary from employ
end

--call/execute the procedure
execute prdemploy1
exec prdemploy1
sp_help 'prdemploy1'
sp_helptext 'prdemploy1'
alter proc prdemploy1 with encryption as
begin
select empid,fname,lname,salary from employ
end

--procedure with a input parameter
create procedure prdemploy2 (@deptid char(4))
as
begin
select empid,fname,lname,salary,deptid from employ where deptid=@deptid
end
exec prdemploy2 'D01'
exec prdemploy2 'D02'
exec prdemploy2 'D03'

create proc p1
as
begin
print 'proc 1'
exec p2
end

create proc p2
as
begin
print 'proc 2'
exec p3
end

create proc p3
as
begin
print 'proc 3'
exec p1
end
drop procedure p2

/* assignment1
create a table of order details with columns orderid,itemid,and quantity ordered
insert records to the table through a procdure */

create table ordrdetails(orderid char(3),itemid char(3),qty smallint)
create procedure prcorder(@o char(3),@i char(3),@q smallint)
as
begin
insert into ordrdetails values (@o,@i,@q)
end

exec prcorder 'O01','I01',10
exec prcorder 'O06','I06',20
exec prcorder 'O07','I07',70
exec prcorder 'O09','I09',40

select * from ordrdetails
alter table ordrdetails alter column orderid char(3) not null
alter table ordrdetails alter column itemid char(3) not null
alter table ordrdetails add constraint pkcomposite primary key(orderid,itemid)

create procedure prdemploy3(@deptid char(4))
as
begin
if exists(select empid,fname,lname,salary from employwhere deptid=@deptid)
return 1
else
return 0
end
declare
exec @var int @var=prdemploy3 'D01'
print @var

alter procedure prdemploy4(@deptid char(4))
as
begin
if exists(select empid,fname,lname,salary from employwhere deptid=@deptid)
return '65'else
return '98'
end
declare @varch varchar(20)
exec @varch=prdemploy4 'D01'
print @varch

create procedure prclist (@empid char(4),@fname char(15) output,@salary int output)
as
begin
set @fname=(select fname from employ where empid=@empid)
set @salary=(select salary from employ where empid=@empid)
end
declare
exec
print @fname char(15),@salary int prclist 'E04', @fname output, @salary output @fname
print @salary
select fname, salary from employ

-- Functions
create function dbo.countemploy() returns
as
declare
select int begin @total int @total= COUNT(empid) from employ
return @total
end

-- inline function
create function getdeptemploys(@deptid char(4))returns table
as
return(select empid,fname,lname,salary from employ where deptid=@deptid)
select * from getdeptemploys('D02')

-- multi-statement table function
drop function empnames
create function empnames(@format varchar(12)) returns @tblemp table(eid char(4) primary key,ename varchar(40))
as
begin
if(@format='shortname')
insert @tblemp
select empid,lname from employ
else if(@format='longname')
insert @tblemp
select empid,(fname+' '+lname) from employ
return
end
select * from empnames('shortname')
select * from empnames('longname')

-- write a function which will take date of birth as input parameter and returns a string containing no of years, months and days

begin
declare
alter function birthdate(@dob date) returns varchar(30) as @yy int,@mm int,@dd int
set @yy=DATEDIFF(yy,@dob,GETDATE())
set @mm=(DATEDIFF(yy,@dob,GETDATE())*12)-(DATEDIFF(mm,@dob,GETDATE()))
set @dd=DATEDIFF(dd,@dob,GETDATE())declare @str varchar(30)
set @str=convert(varchar(4),@yy)+'years'+convert(varchar(4),@mm)+' months '+  convert(varchar(4),  @dd)+'days'
return @str
end
select dbo.birthdate('08-june-91') as age

Cheers..!!
https://techiesss.blogspot.in/