Implementing Functions…
Out of the box SQL Server provides built in functions that you can use throughout your code. One example is the GETDATE() Function. SQL Server also allows you to build your own functions allowing you to create and then use it accross multiple applications. These functions are normally called UDFs.
There are two types of UDFs, Scalar; which return a scalar value result. And table-valued functions, which return in the form of a table result
These two types can either be what is called Deterministic or Nondeterministic. Deterministc meaning that everytime the function is used the same value will be returned. Nondeterministic is the exact opposite in that it will return a different value everyime it is called.
Now lets practice. The practice uses the AdventureWorks sample database which can be downloaded from the web.
We want to create a scalar function to return the model name for a prodruct given a particular product ID. Copy and paste the code into SSMS to test it out.
USE AdventureWorks
GO
CREATE FUNCTION dbo.GetModelNameForProduct (@ProductID int)
RETURNS nvarchar(50)
–Above Creates the function name and the specifies the input parameter
–as well as defining the output variable.
WITH EXECUTE AS CALLER
–Above Specifies the statements inside the module are executed in the
–context of the caller of the module.
AS
BEGIN
DECLARE @ModelName nvarchar(50)
SELECT @ModelName = Production.ProductModel.Name
FROM Production.Product INNER JOIN Production.ProductModel
ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID
WHERE Production.Product.ProductID = @ProductID
RETURN(@ModelName)
END;
–Above declares a variable “ModelName” which is used in the query to return the ProductID
–that is passed as the input when the function is called.
GO
SELECT dbo.GetModelNameForProduct(710);
–The above select statement will test the function out. Run using different inputs to test.
