Parameterized view in SQL Server with example

Parameterized view means we can pass some value to get the data from the view the table by using view. The parameter accepts values that can be supplied later by prompting the user or programmatically, it is possible in MS-Access and FoxPro but is it supported in SQL Server? Answer is big NO, but there are ways to achieve the same feature, which we will discuss in this article.

By using view we can search, update, insert and delete the records from the table on which the view is created but whatever we are going to discuss will allow only the very first option “SEARCH” and we cannot use it to update, insert or delete records into the parent table.

As I said, there is no way to create parameterized view so what are the possible ways to get the data by using parameters. Here is the list of options, which we will see with examples.

1. With common table expression
2. Table variable
3. Table-Valued Function

Common Table Expression (CTE):

The good thing about the common table expression is that you can use it as a view, no need to create the variable like temp table and worried to delete at the end. You can also see it as an inline view. Let’s take an example of a table having many columns but we want only some columns from them say Id, ContactName, City, Address, ZipCode and Phone on the basis of any criteria say salary, so let’s create our common table expression (view)

DECLARE @Salary Decimal
;WITH MyView AS (
   Select Id, ContactName, City, Address, ZipCode, Phone
From Customers
Where Salary > @Salary
)    
--Now MyView can be used as a view, see this
Select * From MyView

If you can note, I used ;WITH according to rule it should be the very first line of the statement but on the first line we declare a variable so by using semi-colon we can fix this issue, one more thing we need to know, common table express can be used only once, I mean to say after selecting data from MyView you cannot use it any more. If you want to use some calculated value then you have to give a column name, see the following example, I used CTE in a procedure

CREATE PROCEDURE TimesheetEntries
   @startDate   DateTime 
  ,@EndDate DateTime 
AS
WITH TimesheetEntries AS 
(
 SELECT top 4 TS.ID [Timesheet]
  ,CreatedDate [CreationDate]
  ,CompletionDate
  ,TaskID
  ,CompletedBy
  ,dbo.USERNAME(CompletedBy) [UserName]
  ,CAST(StartTime AS DATETIME)[StartTime]
  ,CAST(EndTime AS DATETIME)[EndTime]
  ,DATEDIFF(MINUTE, CAST(StartTime AS DATETIME), CAST(EndTime AS DATETIME) )[TotalMinutes]
  From dbo.Timesheet TS
  WHERE CompletionDate BETWEEN @startDate AND @EndDate
)
Select * From TimesheetEntries

Table Variable

Similar to any other variable we can create table variable in SQL Server and fill it by using Insert Into and Select statement, you don’t need to delete it like the temp variable also can be used as many times as you want, we can manipulate it’s data as well. The big pain is that we need to define it but quite handy. Let’s see an example with it

CREATE PROCEDURE ProcTableVariable
  @startDate    DateTime 
 ,@EndDate          DateTime 
AS

DECLARE @TimesheetEntries TABLE
(
 TimesheetID        INT, 
 CreationDate   DateTime,
 CompletionDate DateTime,       
 TaskID         INT,
 CompletedBy        INT,
 UserName       Varchar(50),
 StartTime      DateTime,
 EndTime            DateTime,
 TotalMinutes   INT
)

INSERT INTO @TimesheetEntries
SELECT TS.ID
 ,CreatedDate [CreationDate]
 ,CompletionDate
 ,TaskID
 ,CompletedBy
 ,dbo.USERNAME(CompletedBy) [UserName]
 ,CAST(StartTime AS DATETIME)[StartTime]
 ,CAST(EndTime AS DATETIME)[EndTime]
 ,DATEDIFF(MINUTE, CAST(StartTime AS DATETIME), CAST(EndTime AS DATETIME) )[TotalMinutes]
From dbo.Timesheet TS
WHERE CompletionDate BETWEEN @startDate AND @EndDate

SELECT  * From @TimesheetEntries

First we declare a table variable @TimesheetEntries as Table and defined it’s columns and finally we used Insert Into to fill the data by using parameters, the problem is its execution plan, if you will check you will understand what I want to say, It is slow.

Table Valued Function

User defined functions are of two types Table-Valued Function and Scalar-Valued Function. In scalar valued functions we can only return a single value but to return a complete table or tabular data then we can use Table-Valued Function. As we are discussing about the parameterized view, we can use it to return our result, let’s see the basic code

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(   
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)

As you can see in basic structure it takes parameter (optional), then we need to define what we have to return so it uses RETURNS TABLE AS Return (our query), let's implement it in a real example

CREATE FUNCTION fnTimesheet
(
  @startDate  DateTime 
 ,@EndDate    DateTime 
)
RETURNS Table
AS
RETURN
(
  SELECT TS.ID [Timesheet]
  ,CreatedDate [CreationDate]
  ,CompletionDate
  ,TaskID
  ,CompletedBy
  ,dbo.USERNAME(CompletedBy) [UserName]
  ,CAST(StartTime AS DATETIME)[StartTime]
  ,CAST(EndTime AS DATETIME)[EndTime]
  ,DATEDIFF(MINUTE, CAST(StartTime AS DATETIME), CAST(EndTime AS DATETIME) )[TotalMinutes]
From dbo.Timesheet TS
WHERE CompletionDate BETWEEN @startDate AND @EndDate
)

Now we can use this function as a table, see this

Select * 
   From dbo.TimesheetEntries('2012-10-02', '2012-10-05')
GO

By using Table-Valued Function we can get a better speed, execution plan will be very clear and all the data will be directly selected from the main tables, and it is fast as well.

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 1,084

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 924

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 3,311

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 666

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 12,556