SQL Functions ,Procedures,Triggers

-- Simple XMl Conversion

declare @xml xml

set @xml=(select * from Customers for xml auto,elements )

select @xml as [xml]

--Create a function

create function GetProducts(@catId int)

returns table

as

return

select ProductID,ProductName

from Products

where CategoryID=@catid

--Result:

select *  from GetProducts(5)

select Categories.CategoryName,Categories.CategoryID,productname from Categories

Cross apply

GetProducts(Categories.CategoryID)

--Procedures

create table cust1(CustomerId int primary key,CompanyName varchar(20))

select * from cust1

Crate procedure InsertUpdateCust1

@custId int=null,

@comapnyName varchar(20)=null

as

begin

insert into cust1(CustomerId

                     ,CompanyName)

               values(@custId,@comapnyName)

end

exec InsertUpdateCust1 @custId=1,@comapnyName='TXS'

---Triggers

create table cust1_deleted(CustomerId int primary key,CompanyName varchar(20),remarks nchar(50))

Create procedure DeleteCust1

@custId int

as

begin

delete from cust1 where CustomerId=@custId

end

Exec DeleteCust1 @custId=3

--Triggers

Create  trigger trgDeletecust1

on cust1

for delete

as

begin

insert into cust1_deleted(CustomerId

                               ,CompanyName

                               ,remarks)

                                select c.CustomerId

                            ,c.CompanyName                   

                             ,'Record Deleted Successfully'

                  from deleted c

--select * from cust1_deleted

end