יול14
Written by:
ronen ariely
14/07/2022 00:38 
Note! This discussion is only theoretical and only focus on the question. It does not discuss for example the fact that you should not use "SELECT *" in most cases and the potential issue which can arise by using a dynamic structure for the view...
The question asked in the forum
how to pass parameters to view in SQL Server. I need create views with variable as below.
Create view view_name asSelect * from table where tas_id = V_Taskid
V_taskid is variable which are passing during run time from ADF pipeline
Is it possible to pass variable like above in view
Please suggest.
Thanks
Preparation
First let's focus on why this question is problematic and why solutions which we might think about in first glance are not supported. Secondly I will present some tricks to solve the request. But then we will move to the practical solution since this question is sound like an XY Problem, as I will explain.
But before we start, For the sake of the discussion I will create two tables
----------------------------------- DDL + DML
use tempdb
GO
DROP TABLE IF EXISTS T
GO
CREATE TABLE T(ID int, txt VARCHAR(100))
GO
INSERT T (ID,txt) values
(1,'f'),
(2,'d'),
(3,'s'),
(4,'a')
GO
DROP TABLE IF EXISTS T1
GO
CREATE TABLE T1(ID int)
GO
INSERT T1 (ID) values
(4)
GO
select * from T
select * from T1
GO
What is not supported
Let's start with what is NOT supported and shot reason
(1) Not like a stored procedure or a non-inline function, a view cannot include multiple statement which is why you cannot declare a variable inside the view and then run the SELECT.
-- You can try with or without the parentheses
CREATE VIEW V as (
DECLARE @_id INT
SET @_id = 3
SELECT * FROM T WHERE ID = @_id
)
GO
-- Error: Incorrect syntax near the keyword 'DECLARE'.
(2) A view is not a function (method) and not a stored procedure which get input of parameters. So, You also cannot input a variable to a view statement.
CREATE VIEW V (@_id INT) as
SELECT * FROM T WHERE ID = @_id
GO
-- Error: Incorrect syntax near '@_id'.
(3) A view is kind of an isolated statement. You cannot declare a variable and use it inside the view.
DECLARE @_id INT
SET @_id = 3;
CREATE VIEW V as
SELECT * FROM T WHERE ID = @_id
GO
-- 'CREATE VIEW' must be the first statement in a query batch.
Solution: Using Session Key/Value (added in SQL Server 2016)
In SQL Server 2016 a new feature was added for using a pair of key-value at the session level. We can use the stored procedure sp_set_session_context to set value to specific key and we can use the function SESSION_CONTEXT in order to get the value for specific key.
basically instead of DECLARE variable we will set new key, and instead of using the variable we will use the value of the key.
---------------------------------------------------- Trick one!!!
-- declare session variable and use in the view
sp_set_session_context MyKey, 1
SELECT SESSION_CONTEXT(N'MyKey');
GO
CREATE OR ALTER VIEW V as
SELECT * FROM T WHERE ID = SESSION_CONTEXT(N'MyKey')
GO
SELECT * FROM V
GO
Sound an XY problem...
OK... I gave a direct answer to the question, but is this the right solution for what is really needed?!?
This question sound like an "XY problem", where someone ask something specific, while the solution is probably totally different, since this is not the real source of what is needed but a request to solve something after you already got a wrong road for solving what you really need.
| > From Wikipedia: The XY problem is a communication problem encountered in help desk, technical support, software engineering, or customer service situations where the question is about an end user's attempted solution (Y) rather than the root problem itself (X). |
My guess is the value which you want to use in the VIEW as variable is not a constant value, but you get it from somewhere like a "previous" query which get the value and then you want to use it.
In this case the solution is NOT to use a value as part of the VIEW but to find the value of the parameter as part of the VIEW statement using for example CTE
CREATE OR ALTER VIEW V as
WITH MyCTE AS (SELECT TOP 1 ID FROM T1) -- here we find the value of what you wanted to use in the VIEW
SELECT T.* FROM T
INNER JOIN MyCTE ON MyCTE.ID = T.ID
GO
SELECT * FROM V
GO
I hope that this was useful and again, please remember that the demo here are only for the sake of the explanation and you should not write lazy code as above!
Read more