Pass parameters to view in SQL Server

In this SQL Server tutorial, we will discuss how to pass parameters to view in SQL Server. Further, we will also illustrate the following topics in this tutorial.

• Can we pass parameters to a view in SQL Server?
• Can we parameterize a view in SQL Server?
• Can we use variables in SQL Server View?
• Create view with if condition in SQL Server
• SQL Server View with WHERE clause
• How to crete updateble view in SQL Server
• How to pass parameters to view in SQL Server

Can we pass parameters to a view in SQL Server?

No, in SQL Server, we cannot pass parameters to a view. And it can be considered as one main limitation of using a view in SQL Server. Moreover, even if we try to pass parameters to a view, the SQL Server will return an error.

Let’s understand this limitation using an example in SQL Server. For this example, consider the following SQL query.

USE [sqlserverguides]
GO

CREATE VIEW vw_GetDataByCountry (@country VARCHAR(50))
AS
  SELECT id, customer_name, city, country 
  FROM dbo.Customers
  WHERE country = @country
GO

In the above example, we are creating a view with a parameter country of type VARCHAR(50). But, when we execute the above script, it will return the following error.

Also, check: How to delete a view in SQL Server

Can we parameterize a view in SQL Server?

Before moving forward, we should understand what does the term parameterize a view mean. This term simply refers to creating a parameterized view in SQL Server.

However, the term “parameterized view” refers to the ability to pass a value to the view to obtain some data. Now, this task is feasible in many systems like FoxPro, MS Access, where values can be supplied later by prompting the user or programmatically.

But this task is not supported in SQL Server. Also, in the previous section, we have seen what error does SQL Server returns when we try to create a parameterized view in SQL Server.

Can we use variables in SQL Server View?

No, we cannot use variables in a SQL Server View. This is because a view is just like a SQL statement with a name. Moreover, it does not exist physically in a database in SQL Server.

And similar to a parameter, if we try to declare a variable in a view, the SQL Server will return an error. Let’s understand this limitation using an example in SQL Server. For this example, consider the following SQL query.

USE [sqlserverguides]
GO

CREATE VIEW vw_GetDataByCountry
AS
  DECLARE @country VARCHAR(50)
  SET @country = 'United States'
  SELECT id, customer_name, city, country 
  FROM dbo.Customers
  WHERE country = @country
GO

In the above example, first, we are creating a view. Further, within a view, we are declaring a variable country of type VARCHAR(50). But, when we execute the above script, it will return the following error.

Read: How to call a view in SQL Server

Create a view with if condition in SQL Server

As discussed earlier, a view in SQL Server only consists of a SELECT statement, and we cannot use an IF statement in it. And even if we try to use an IF condition in a view, the SQL Server will return an error.

Let’s understand this limitation using an example in SQL Server. For this example, consider the following SQL query.

Read: SQL Server Trigger After Insert Update

Alternative Methods

Now that we have seen that we cannot use IF statement in a view SQL Server. And it is also a limitation of using a view.

But, what are alternatives, and how we can achieve this condition in SQL Server?

Now, there are many other options in SQL Server. And here are a few solutions other than a view.

• Using a stored procedure: We can declare a variable within a stored procedure. Moreover, we can also use the IF condition within it.
• Using a user-defined function in SQL Server: We can also declare a variable within a SQL Server UDF. Moreover, we can also use the IF condition within it.
• Use a CASE statement: As an alternative, we can use a CASE statement within view to achieve condition similar to IF.

Now, let’s, understand how to use a CASE statement in a view instead of an IF condition in SQL Server.

USE [sqlserverguides]
GO

CREATE VIEW [dbo].[vw_GetCount]
AS
  SELECT [Customer Count] = CASE WHEN COUNT(customer_name) > 0 THEN 
  'Customer count is greater than zero' ELSE 'Customer count is zero' END
  FROM Customers
GO

In the above example, we are using the CASE statement within the view to check the count of customers. And when we query the view, we will get an output similar to the image below.

Read: Indexed views in SQL Server

SQL Server View with WHERE clause

In SQL Server, a view is just like a SQL query stored with a name in a database. Now, a view mainly consists of a SELECT statement and we can easily use WHERE clause with a SELECT statement.

Let’s understand how to create a view in SQL Server with a WHERE clause. For this, consider the following example in SQL Server.

USE [sqlserverguides]
GO

CREATE VIEW [dbo].[vw_USACustomers]
AS
SELECT id, customer_name, city, country
FROM Customers
WHERE country = 'United States'

GO

In the above example, we have created a view in the sqlserverguides database with the name  vw_USACustomers. And this view will return the customer’s id, name, city, and country from the Customers table where the country is the United States.

Now, to simply get the result, we can use the SELECT statement to fetch columns from view.

SELECT * FROM [dbo].[vw_USACustomers];

Here is the image showing the final result of the above query.

Read: How to use union in view SQL Server

How to create updatable view in SQL Server

Now, by an updatable view in SQL Server, we mean using a view to update the data of an underlying table in SQL Server. And we have already illustrated the rules of creating an updatable view in the following tutorial.

How to pass parameters to view in SQL Server

As discussed earlier, in SQL Server, we can not pass parameters to a view. But, there is one more trick to pass a parameter to a view. And in this section, we will illustrate this alternative method and also provide a working example.

Now, for this implementation, we will use the SESSION_CONTEXT function in SQL Server. This function is mainly used to set and read the specified key-value in the current session.

But, to use this function, first, we need to set the value of the key and for this, we need to use the sp_set_session_context stored procedure.

Let’s understand the syntax of using a SESSION_CONTEXT function in SQL Server.

EXEC sp_set_session_context
  @key = N'key_name',
  @value = Key_value;

SELECT SESSION_CONTEXT(N'key_name');

• In the above syntax, first, we are executing the sp_set_session_context procedure with some parameters.
• The first parameter is @key, it is just like a key, and the second parameter is the @value for the key.
• And then, we are just using the SELECT statement to query the key value from the SESSION_CONTEXT function.

Example for alternative method

Now that we know about the SESSION_CONTEXT function, we will use it as a parameter in a view. For this example, consider the following SQL code.

USE [sqlserverguides]
GO

CREATE VIEW vw_USACustomers
AS
SELECT * FROM Customers
WHERE country = SESSION_CONTEXT(N'country_name');

In the above example, we have created a view in the sqlserverguides database with the name  vw_USACustomers. In this view, we are using the SELECT statement to fetch all the columns from the Customers table.

And then, we are using the WHERE clause to filter the records based upon the country name. And fo this filtration, we are using the SESSION_CONTEXT function.

EXEC sp_set_session_context 'country_name', 'United States'
SELECT * FROM vw_USACustomers

Next, we need to set the value for the key specified in the SESSION_CONTEXT function. In the end, we can query the view. Here is the output of the example.

You may also like to read the following SQL Server tutorial.

• SQL Server Date Format 
• SQL Server Trigger Update
• Instead of Trigger In SQL Server
• SQL Server logical operators and example
• Comparison Operators in SQL Server
• Arithmetic operators in SQL Server
• How to view table in SQL Server
• Temp table in stored procedure in SQL Server

So, in this tutorial, we have discussed how to pass parameters to view in SQL Server. Further, we have also illustrated the following topics in this tutorial.

• Can we pass parameters to a view in SQL Server?
• Can we parameterize a view in SQL Server?
• Can we use variables in SQL Server View?
• Create view with if condition in SQL Server
• SQL Server View with WHERE clause
• How to crete updateble view in SQL Server
• How to pass parameters to view in SQL Server