SQL Question 20

Problem

  • Use Northwind
  • Write a multistatement table valued function that returns a product list by Supplier. The function takes a Supplier NAME as a parameter and returns a table with the product id, productname and unitprice. Test whether the Supplier Name is in the supplier table, and if not return an error message in the table otherwise use the SupplierID to return only those products for that Supplier.
  • Test the function with the Supplier data.

Code

USE Northwind
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_ProductsBySupplier]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_ProductsBySupplier]
GO
CREATE FUNCTION udf_ProductsBySupplier (@suppliername varchar(1000))
RETURNS @t TABLE(
ProductID int,
ProductName varchar(50),
unitPrice money)
AS
BEGIN
DECLARE @supplierID int
IF @suppliername IN (SELECT ContactName FROM Suppliers)
BEGIN
SET @supplierID = (SELECT SupplierID FROM Suppliers WHERE ContactName=@suppliername)
INSERT INTO @t(ProductID, ProductName, unitPrice)
SELECT ProductID, ProductName, UnitPrice
FROM Products p
WHERE SupplierID = @supplierID
END
ELSE
INSERT INTO @t VALUES (null,'error',0)
RETURN
END
-- test function
GO
SELECT s.SupplierID, s.ContactName, uf.ProductID, uf.ProductName, uf.UnitPrice
FROM Suppliers s CROSS APPLY udf_ProductsBySupplier(s.contactname) uf
ORDER BY 1
--Bad input test
SELECT * FROM udf_ProductsBySupplier(100)

Result

Question 20 Result

Question 20 Result

Question 20 Bad Test

Question 20 Bad Test

Notes

?

No Comments Yet.

Leave a comment