Sql server® 2012 t-sql fundamentals

190  

Microsoft SQL Server 2012 T-SQL Fundamentals

5-1

This exercise requires you to define a function called TopProducts that accepts a supplier ID (@supid) 

and a number (@n), and is supposed to return the @n most expensive products supplied by the input 

supplier ID. Here’s how the function definition should look.

USE TSQL2012; 

IF OBJECT_ID('Production.TopProducts') IS NOT NULL 

  DROP FUNCTION Production.TopProducts; 

GO 

CREATE FUNCTION Production.TopProducts 

  (@supid AS INT, @n AS INT) 

  RETURNS TABLE 

AS 

RETURN 

  SELECT TOP (@n) productid, productname, unitprice 

  FROM Production.Products 

  WHERE supplierid = @supid 

  ORDER BY unitprice DESC; 

GO

Starting with SQL Server 2012, you can use the OFFSET-FETCH filter instead of TOP. You would 

replace the inner query in the function with the following one.

  SELECT productid, productname, unitprice 

  FROM Production.Products 

  WHERE supplierid = @supid 

  ORDER BY unitprice DESC 

  OFFSET 0 ROWS FETCH FIRST @n ROWS ONLY;

5-2

In this exercise, you write a query against the Production.Suppliers table and use the CROSS APPLY op-

erator to apply the function you defined in the previous step to each supplier. Your query is supposed 

to return the two most expensive products for each supplier. Here’s the solution query.

SELECT S.supplierid, S.companyname, P.productid, P.productname, P.unitprice 

FROM Production.Suppliers AS S 

  CROSS APPLY Production.TopProducts(S.supplierid, 2) AS P;

www.it-ebooks.info

---