-- 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