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.
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
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.
Having 10+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 8 years and still with the same company. Always ready to learn new technologies and tricks.
|By Ali Adravi On 07 Sep, 13 Viewed: 695|
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: 359
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: 482
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: 1,157
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: 329
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: 4,843