DRAG DROP
You write the following SELECT statement to get the last order date for a particular customer.
You need to create the user-defined function to return the last order date for the specified
customer.
Which five Transact-SQL statements should you use? (To answer, move the appropriate SQL
statements from the list of statements to the answer area and arrange them in the correct order.)
Answer: See the explanation
Explanation:
Box 1:Box 2:
Box 3:
Box 4:
Box 5:
Note:
* First function header
* Then declare that the function returns a datetime
* Thirdly begin the function body.
* Fourthly declare the return variable
* At last include the code that retrieves the required date.
a working example of this in the adventureworks db
CREATE FUNCTION dbo.ufnGetLastModifiedDate (@CustomerId int)
RETURNS datetime
AS
BEGIN
DECLARE @OrderDate datetime
SELECT @OrderDate = MAX(ModifiedDate)
FROM Sales.Customer
WHERE CustomerID = @CustomerID
RETURN @OrderDate
END
— then
select dbo.ufnGetLastModifiedDate(1) AS myresult
create function dbo.ufdGetLastOrderDAte (@CustomerID int)
returns
datetime
as
begin
declare @OrderDate datetime
select @OrderDate=MAX(OrderDate) as OrderDate
from Sales
where CustomerID=@CustomerID
return @OrderDate
end