Friday 11 October 2013

05. Create a function that returns a table containing the ID and the name of the customers who are categorized as individual customers (CustomerType = 'I'). The function should take one parameter. The parameter value can be either Shortname or Longname. If the parameter value is Shortname, only the last name of the customer will be retrieved. If the parameter value is Longname, then the full name will be retrieved.

create Function sales.IndividualDetails(@format nvarchar(9))
Returns @tbl_individual table(CustomerID int Primary key, Name Nvarchar(100))
as
begin if (@format='LongName')
insert @tbl_Individual
select Cu.CustomerID,FirstName+''+LastName
from Person.Contact as c join sales.Individual as l
on c.ContactID=l.ContactID join sales.Customer as Cu
on l.CustomerID=cu.CustomerID
Where cu.CustomerType='l'Order by LastName,FirstName
Else
If (@Format='ShortName')
insert @tbl_individual
select cu.customerID,LastName from Person.Contact as c join
sales.Individual as l on c.ContactID=l.ContactID join sales.Customer as Cu
on l.CustomerID=cu.CustomerID
Where cu.CustomerType='l'
Order by lastName
return
end


select * from Sales.IndividualDetails('LongName')
select * from Sales.IndividualDetails('ShortName')

No comments:

Post a Comment