SQL-Implementing Stored Procedures and Functions

• Exercise 1

Create a batch that finds the average pay rate of the employees and then list the details of the employees who have a pay rate less than the average pay rate.

declare @avg_rate int
select @avg_rate = AVG(rate)
from HumanResources.EmployeePayHistory
select * from HumanResources.EmployeePayHistory
where rate < @avg_rate

• Exercise 2

Create a function that returns the shipment date of a particular order

create function Sales.CalShipDate(@SalesOrderID int)
returns Date
as
begin
declare @shipDate Datetime
select @shipDate=ShipDate
from Sales.SalesOrderHeader where SalesOrderID = 

@SalesOrderID
if (@shipDate IS NULL)
set @shipdate = 0
return @shipDate
end

select salesOrderID, Sales.CalShipDate(salesOrderID)
as ShippingDate
from Sales.SalesOrderHeader

• Exercise 3

Create a function that returns the credit card number for a particular order.

create function Sales.DisplayCardNumber(@SalesOrderID int)
returns nvarchar(25)
as
begin
declare @ret nvarchar(25)
select @ret = CardNumber
from Sales.SalesOrderHeader s join sales.CreditCard c
on s.CreditCard = c.CreditCard
where salesOrderID = @SalesOrderID
if(@ret IS NULL)
set @ret =0
return @ret
end

select SalesOrderID, 'Credit Card Number' = Sales.DisplayCardNumber(SalesOrderID) from 
Sales.SalesOrderHeader

• Exercise 4

Create a function that returns a table containing the ID and the name of the customers

create function sales.IndividualDetails (@format nvarchar(9))
returns @tbl_IndividualTable
(CustomerID int PrimaryKey, Name nvarchar(100))
as begin if(@format ='LONGNAME')
insert @tbl_IndividualTable
select Cu.CustomerID,FirstName+''+LastName
from PersonContact as C
join Sales.Individual as I
on C.ContactID=I.ContactID join Sales.Customer as Cu
on I.CustomerID = Cu.CustomerID
where Cu.CustomerType='I'
order by LastName,FirstName
else if(@format='SHORTNAME')
insert @tbl_Individual
select Cu.CustomerID,LastName from Person,Contact as C
join Sales.Individual as I on C.ContactID = I.ContactID
join sales.Customer as Cu on I.CustomerID = Cu.CustomerID where Cu.CustomerType = 'I'
order by LastName
return
end

select * from Sales.IndividualDetails('LONGNAME')
select * from Sales.IndividualDetails('SHORTNAME')

• Exercise 5

Create a user-defined function that accept the account number of customer and returns...

create function fx_Disp_AccDet (@AccNum int)
returns table with schemabinding
as
return(
select Customer_name, Acc_num
from dbo.Depositor
where Acc_num = @AccNum
)

select * from fx_Disp_AccDet(101)

• Exercise 6

Create a batch to check the availability of the product, Chaining Bolts, in the stock of the database

declare @MakeFlag bit
select @MakeFlag = MakeFlag from Production.Product
where Name = 'Chaining Bolts'
if @MakeFlag = 1
print 'The Stock is available'
else
print 'The Stock is not available'

• Exercise 7

Create a stored procedure that accept the name of a product and display its ID, number and availability

create proc productList @name varchar(50)
as
begin
print 'Product Details'
select ProductID, ProductNumber, MakeFlag as Availability
from Production.Product
where Name = @name
end 

execute productList 'Keyed Washer'

• Exercise 8

Create a stored procedure that returns the standard cost of a given product

create procedure prcGetCostDetails @ProductId int
@StandardCost money output
as
begin
if exists(select * from Production.ProductCostHistory
where ProductID = @productID)
begin
select @StandardCost = StandardCost
from Production.ProductionCostHistory
return 0
end
else
return 1
end

• Exercise 9

Create a stored procedure that accepts two number, num1 and num2 and display the result after these 
numbers

create procedure vx_DIV @Num1 int,@Num2 int
as
begin
declare @Div int
if @Num1 < @Num2
raiserror('You have entered your number in the wrong  way',16,1)
else
set @Div=@Num1/@Num2
print @Div
end