/****************************************************** * * Name: scalar-value-function.sql * * Design Phase: * Author: John Miner * Date: 01-04-2013 * Blog: www.craftydba.com * * Purpose: Create a series of articles on * database developer (70-433) topics. * * Topic: Talk about scalar value functions. * ******************************************************/ -- Use the correct database USE [AdventureWorks2012] GO -- Delete existing scalar valued function IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnGetStock]') AND [type] in (N'FN') ) DROP FUNCTION [dbo].[ufnGetStock] GO -- Create stub scalar valued function CREATE FUNCTION [dbo].[ufnGetStock] (@ProductID [int]) RETURNS [int] AS BEGIN RETURN 1; END GO -- Alter sclar valued function ALTER FUNCTION [dbo].[ufnGetStock] (@ProductID [int]) RETURNS [int] AS BEGIN -- Declare local variable DECLARE @ret int; -- Only look at inventory in the misc storage SELECT @ret = SUM(p.[Quantity]) FROM [Production].[ProductInventory] p WHERE p.[ProductID] = @ProductID AND p.[LocationID] = '6'; -- Return the result IF (@ret IS NULL) SET @ret = 0; RETURN @ret; END GO -- Example use of the function SELECT ProductId, [name] as ProductName, [dbo].[ufnGetStock](ProductId) as StockLevel FROM [Production].[Product] WHERE [name] like '%seat%';