Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Use a Function as a Parameterized View! The Best Thing Since Sliced Bread

  • 4 September 2012
  • Author: bryan_cafferky
  • Number of views: 7577
  • 0 Comments

You have a query that gets data perfectly and you would like to make into a view so you can use it in a number of places without re-coding.  But there’s a problem, it requires parameters such as a date range filter and SQL Server views do not support parameters.  You can use a stored procedure but that limits the number of ways it can be called. The answer?  You can create a function that acts like a parameterized view.

Every now and then you find a feature of SQL Server that gets you all excited and you wonder how you missed it.  The ability to create functions that return data was one of those times for me and I want to share with you how you can add this technique to your arsenal. 

To make the example easy for you to run, I used Adventure Works and did something similar to the situation I actually faced. In my case, the view needed to return data as of a point in time for financial reporting.  I had to extract detailed order data based on the order date but then summarize it.  Since the data returned did not include the order dates, I could not use a view and then filter on the order date.  Instead I created a function that used a Common Table Expression (CTE) to summarize the order amount filtered for orders placed between the dates passed in. 

First, I’ll just show you the code for the function and then I’ll explain how it works in detail.

 

USE AdventureWorks 

-- Author: Bryan Caferky, BI TEam   09/04/2012
--
-- Project:  Adventure Works
--
-- Purpose:   Return sales by sales rep for a selected time period.
--
-- Sample call: 
--    select * from [dbo].[fn_Get_Sales_By_SalesPerson]('2001-07-01', '2001-07-31')


Create FUNCTION [dbo].[fn_Get_Sales_By_SalesPerson]
    (
      @start_date    DATE,
      @end_date      DATE
    )
RETURNS @retData TABLE 
(
   	[SalesPersonID]          int              NULL,
	[LastName]               VARCHAR(50)      NULL,
	[FirstName]              VARCHAR(50)      NULL,
	[Total_Sales]            decimal(18, 2)   NULL
)
AS 
    BEGIN
;    
WITH cte_sales AS (
SELECT [SalesPersonID],
        CAST(SUM([TotalDue]) AS DECIMAL(18,2)) AS Total_Sales
FROM [AdventureWorks].[Sales].[SalesOrderHeader] 
WHERE duedate >= @start_date    AND duedate <= @end_date      
GROUP BY [SalesPersonID]
)

 INSERT @RetData
 SELECT s.[SalesPersonID]
      , con.LastName
      , con.FirstName
      , [Total_Sales] 
--        INTO dbo.tmp_sales    
  FROM cte_sales s
  JOIN [AdventureWorks].[HumanResources].[Employee]     emp  
       ON (s.SalesPersonID = emp.EmployeeID)
  JOIN [AdventureWorks].[Person].[Contact]              con         
       ON (emp.ContactID = con.ContactID)
  
 
  RETURN 

END
GO

 

Voila, A Parameterized View…

One of the best things about this is that you can treat this function like a view which means you can filter and sort on it.  So the following is perfectly fine:

 

Use AdventureWorks
 
select * from [dbo].[fn_Get_Sales_By_SalesPerson]('2001-07-01', '2001-07-31')
Where SalesPersonID > 279
Order By LastName


About the code…

 

The code uses the CREATE FUNCTION and supports passing two parameters which are @start_date and @end_date.  Since this function will return data much like a view, you need to define a TABLE variable which is what the statement ‘RETURNS @retData TABLE’ does. Then you enclose the columns that will be passed back inside parenthesis.

The function block must start with a BEGIN statement and end with the END statement. This is just a requirement SQL Server imposes on this type of function. 

I have a Common Table Expression (CTE) that summarizes the orders filtering on the input parameters. Note: Alhtough I filter on the input dates, I am not including the dates in the data being returned which is why I cannot just define this as a view and use my query against it to apply a filter.

You tell SQL Server what data you want to send back via the INSERT @TableVariable statement where @TableVariable is
the name of the table variable you specified in the RETURNS statement earlier so here we have INSERT @retData. 

Then you just code the select statement for the data you want to send back to the client calling the function.

Pros and Cons

Using funtions to get data offers a lot of advantages but it is not a solution for every situation. Some things to consider include:

  • This method uses a TABLE variable to return the data which may not scale well for large datasets.
  • Be careful if end usersinteract with one of these functions.  If they enter the wrong paramter values, a runaway query may result.
  • It is more difficult to optimize a function because the actual query is not known until run time.  Of course, this can depend on what how muchthe submitted SQL can vary.

A Trick to Make This Easier

If you have a lot of columns to return, you can use a trick to make entering the column definitions easier.  To do this,  take the select statement that will return your data in the function and add the INTO clause with a table name that will be created.   Then comment out the‘INSERT @RetData’ statement.  Now just highlight the SQL in the function needed to get data and click ‘Execute’ And then run the query.  It will create a table with the columns from the query.   The modified code is shown below.

 

;    
WITH cte_sales AS (
SELECT [SalesPersonID],
        CAST(SUM([TotalDue]) AS DECIMAL(18,2)) AS Total_Sales
FROM [AdventureWorks].[Sales].[SalesOrderHeader] 
WHERE duedate >= '2001-07-01' AND duedate <= '2001-07-31'
GROUP BY [SalesPersonID]
)

--INSERT @RetData
 SELECT s.[SalesPersonID]
      , con.LastName
      , con.FirstName
      , [Total_Sales] 
       INTO dbo.tmp_sales    
  FROM cte_sales s
  JOIN [AdventureWorks].[HumanResources].[Employee]     emp  
       ON (s.SalesPersonID = emp.EmployeeID)
  JOIN [AdventureWorks].[Person].[Contact]              con         
       ON (emp.ContactID = con.ContactID)

 

Then find the new table in SQL Server Management Studio (SSMS), right click on it, select ‘Script Table as’, ‘CREATE To’,  “New Query Editor Window’ and the table definition will appear in a new window.

Get the Table Definition

 

Now just copy the column defintions…

Copy the column definitions

And paste it into line in your script that defines the table to be returned...

 

RETURNS @retData TABLE 
(
… Paste the code here…
)


So the code looks as follows...

 

 

RETURNS @retData TABLE 
(
	[SalesPersonID] [int] NULL,
	[LastName] 		      [dbo].[Name] NOT NULL,
	[FirstName]        	      [dbo].[Name] NOT NULL,
	[Total_Sales] 	      [decimal](18, 2) NULL
)
 


Note: In this case, Adventure Works had a data type called ‘Name’ defined so it uses that in the table it creates.  That’s Ok but I preferred to use the basic SQL types of varchar instead in my function instead.

 

It's That Simple...

As powerful as these functions are, they are simple to implement.  Ok, maybe not quite as easy as if SQL Server really had parameterized views but pretty close.  I hope you find this technique as useful as I do.

Thanks,

Bryan Cafferky, Senior BI Engineer

Print
Tags:
Rate this article:
No rating

bryan_cafferkybryan_cafferky

Other posts by bryan_cafferky

Please login or register to post comments.