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