-- Table Valued Stored Procedure --Creating a table here
create table Candidate
(
id int,
Name varchar(20)
)

insert into candidate values (1,'Adarsh')
insert into candidate values (2,'Rohit')
insert into candidate values (3,'Vinod')
insert into candidate values (4,'Rahul')
select * from Candidate

create procedure ProcONE
as
select * from Candidate
exec ProcONE

--create the User defined table type
create Type MyType as
Table
(
id int,
Name varchar(20)
)

declare @MyTable MyType
insert into @MyTable values (1,'Adarsh'),(2,'Rohit'),(3,'Vinod')
--select * from @MyTable
insert into Candidate
select ID, name from  @MyTable

exec ProcOne

--Stored Procedures to auto Start
use master

--Stored Procedures to auto Start when a Sequel Server Starts
use master
create Procedure Adarsh as print 'Hello Adarsh'

exec sp_procoption Adarsh,'startup','on'

select * from master.INFORMATION_SCHEMA.ROUTINES where OBJECTPROPERTY(object_id(Routine_name),'ExecIsStartup')=1

--Trace flag dbcc traceon(4022)
dbcc tracestatus(-1)
exec sp_procoption Adarsh,'startup','on'

--Recompiling a Stored Procedure
exec sp_recompile 'Adarsh'
--master.dbo.sysCacheObjects store compiled objects
select * from master.dbo.sysCacheObjects where sql like 'exec%'

use Adarsh
create procedure Adarsh
as
select * from Candidate
select * from Candidate
print 'Hello'

exec Adarsh

--Encrypting and Decrypting

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[Adarsh]    Script Date: 09/20/2010 15:32:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create Procedure [dbo].[Adarsh] as print 'Hello Adarsh'
GO

EXEC sp_procoption N'[dbo].[Adarsh]', 'startup', '1'

GO

create Procedure [dbo].[Adarsh1] with encryption
as print 'Hello Adarsh'

sp_helptext Adarsh1
sp_helptext adarsh

--Can we execute a stored Procedure within queries...?
--Cant insert, update or delete any records in a function

select getdate()

--Can be called within a select statement only one way using linked service using open query

use Adarsh
alter procedure Adarsh
as
select * from Candidate
select * from Candidate
print 'Hello'

sp_helptext Adarsh
exec Adarsh

--3 UDFs
Scalar
Inline
Multi Statement Table Valued

alter function fnAdarshRahul
(
@employeename varchar(50)
)
returns int Begin Return (select id from Candidate where name = @employeename)
end

select * from dbo.fnAdarshRahul('Rahul')
select * from Candidate

--Inline Functions
create function fnTable
(
@EmployeeName varchar(50)
)
returns table as
return
select * from candidate where name = @EmployeeName
go

select * from fnTable('Adarsh')

--Multi Statement UDF
--Returns a table datatype

--Functions Cant be used to modify table info..DML,Insert , update and Delete
--Functions that return Non-Deterministic values arent allowed..

--CLR Integration
--Basics of SQLCLR
--Using CLR
--Compiled .net code---Managed Code
--Compiled .net code in files--DLL--Assemblies
--CLR allows creating Aggregate Functions and user defined types unlike T-SQL where in
--it allows only stored Procedures, function and triggers, which can also be creted in CLR
--CLR allows Encryption and Compression which isnt there in T-SQL
--To Perform complex procedural tasks
--To create user defined aggregates and data types

-- Enable CLR Integration in SQL
Reconfigure
sp_configure 'Show advanced options', 1
go
reconfigure
go
sp_configure 'clr enabled',1
reconfigure

--Code that deploys an assembly

______________________________________________________________________________________________
EXCEPTION HANDLING:

ERRORS:
1.) Syntax errors: When keyword and grammer is not properly used
2.) Logical errors: When desired is not there
3.) Exception Errors: External errors happen situationally for divide by zero,primary key errors

Using Try-Catch Exceptions are handled
syntax:
Begin try
--Statements
End try
Begin Catch
--Statements
End Catch

e.g.:

declare @n1 int,@n2 int,@result decimal
Begin try select @n1 = 100,@n2 = 0
select @result = @n1/@n2
print @result
end try
begin catch
print 'Error occured due to divide by zero is encountered:'
print Error_number()
print Error_message()
print Error_Line()
print error_severity ()
end catch

select * from sys.messages where message_id = 8134
select * from sys.syslanguages
select * from sys.sysprocesses

______________________________________________________________________________________________
TRANSACTIONS:

Logical grouping of a  set of DML operations which must execute totally or none

transaction execute statements temporarily in buffer and makes it parmanent when it is committed......
Before committin u may revert or rollback the operations

Transactions gets over either when u rollback or commit the transaction

Types of transactions:
1.) Implicit Transaction: These are initiated by server automatically and committed also. Aotu commit is enabled by default in SQL server,           which can be changed using statements;
         SET IMPLICIT_TRANSACTIONS ON/OFF
2.)Explicit Transaction: Starting and finishing both are done exclusively by users using the block:
         BEGIN TRANSACTION AND ROLLBACK TRANSACTION OR COMMIT TRANSACTION
use employeeDB
select * from employees

set implicit_transactions off    --Default: Server will auto commit for this state
insert into employees values (110,'aaa',1111,210,111,'Elec')
rollback tran
delete from employees where ecode = 110

begin try
begin tran
delete from employees where ecode = 103
--statement1
commit
print 'Records deleted'
--statement2
end try begin catch
--statement1
print 'Error deleting the records'
rollback tran
end catch

Transactions can also be nested.........
For multiple ongoing transactions we use the isolations

set transaction isolation level read committed
--Other transaction will get only committed data
set transaction isolation level read uncommitted
--Other transactions will get uncommitted data also i.e. dirty read

use employeeDB
set transaction isolation level read uncommitted
begin tran
select * from employees where ecode =102

______________________________________________________________________________________________
TOTALLING AND SUB-TOTALLING OF DATA

select deptid,sum(salary) from employees
group by deptid with rolldown

Two-Dimensional Sub Totalling using: PIVOT (Also called as cross tab queries)

create table MonthlySales
(
eid int,
salesamt int,
monthid int
)

insert into MonthlySales values (1,100,1)
insert into MonthlySales values (1,200,2)
insert into MonthlySales values (1,300,3)
insert into MonthlySales values (1,400,4)

insert into MonthlySales values (2,1000,1)
insert into MonthlySales values (2,2000,2)
insert into MonthlySales values (2,3000,3)
insert into MonthlySales values (2,4000,4)

insert into MonthlySales values (3,10000,1)
insert into MonthlySales values (3,20000,2)
insert into MonthlySales values (3,30000,3)
insert into MonthlySales values (3,40000,4)

select * from MonthlySales

select * from MonthlySales PIVOT( sum(salesamt) for monthid IN ([1],[2],[3],[4])) as a

CTE (Common Table Expressions)

with totalsales(eid,m1,m2,m3,m4)
as
(
select * from MonthlySales PIVOT( sum(salesamt) for monthid IN ([1],[2],[3],[4])) as a
)
select sum(m1) "Jan",sum(m2) "Feb",sum(m3) "Mar",sum(m4) "Apr"
from totalsales

______________________________________________________________________________________________
RANKING OF RECORDS:

select Row_number() over(order by ecode) as "Row Number",ecode,ename,salary,deptid,bonus from employees
delete employees where ecode = 110
select Row_number() over(order by (select 1)) as "Row Number",ecode,ename,salary,deptid,bonus from employees

select Row_number() over(partition by deptid order by ecode) as "Row Number",ecode,ename,salary,deptid,bonus from employees --To inturn rank the result set by any column like deptid as in the query above

--Day6 DB-Devolopment
create procedure sp_calbonus_cur (@did int)
as
declare @ecode int, @salary money, @bonus int

open emp_cur
fetch next from emp_cur into @ecode,@salary
while @@fetch_status = 0
begin
print 'Emp Code:' + convert (char(5), @ecode)

select * from dapartment
select * from employees
alter table employees
add bonus float, dname varchar(25)
update employees set bonus = 111, dname = 'Account', salary = 1111 where ecode = 101
insert into employees values (102,'bbb',2222,201,222.2,'Account')
update employees set bonus = 111, dname = 'Account', salary = 1111 where ecode = 103
update employees set deptid = 203 where ecode = 103
select * from employees
update employees set bonus = 444.6, dname = 'Admin', salary = 4444, deptid = 203 where ecode = 104
update dapartment set dname = 'Admin' where deptid = 203
delete employees where ecode = 105
select * from employees order by ecode

______________________________________________________________________________________________
declare @deptid int
declare @ecode int,@ename varchar(20),@salary int,@bonus int,@dname varchar(20)
declare dept_cur cursor
for select deptid from dapartment where deptid in (201,203)
open dept_cur
fetch next from dept_cur into @deptid
while (@@fetch_status = 0)
Begin
Print 'DeparmentID:'+convert(char(5),@deptid)
declare emp_cur cursor
for select ecode,ename,salary,bonus,dname  from employees where deptid = @deptid
open emp_cur
fetch next from emp_cur into @ecode,@ename,@salary,@bonus,@dname
while (@@fetch_status  = 0)
Begin
Print 'ecode ename salary bonus dname '
print convert(char(5),@ecode)+' '+@ename+' '+convert(char(5),@salary)+' '+convert(char(5),@bonus)+' '+@dname
fetch next from emp_cur into @ecode,@ename,@salary,@bonus,@dname
end
close emp_cur
deallocate emp_cur
fetch next from dept_cur into @deptid
end
close dept_cur
deallocate dept_cur

______________________________________________________________________________________________
declare @dbname varchar(25)
declare @cDate datetime
declare @fname varchar(100)
set @cDate = getdate()
declare BAK_Cur cursor
for  select name from master.dbo.sysdatabases
open BAK_Cur
fetch next from BAK_Cur into @dbname
while (@@fetch_status  = 0)
Begin
 select @fname='C:SystemDB'+@dbname+'_'+ convert(char(5),getdate()) +'.BAK'
 backup database @dbname to disk = @fname
 fetch next from BAK_Cur into @dbname
end
close BAK_Cur
deallocate BAK_Cur