Can Views take the Input Parameters

One of my friends is trying to passing some values in views. He told me that is there any options in SQL server to pass the parameters in views. 

The answer is NO. It is not possible at any version of the SQL server as the view is not build for that purpose.

But we have others ways to do that, and we can do it very easily by table value functions.  Here in this article I am trying to demonstrate it easily by taking an example.

-- My base table

CREATE TABLE my_Emplyee

      (ID         INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,

       EMP_NAME   VARCHAR(50)  NULL,

       SAL        DECIMAL(20,2)NOT NULL)

      

-- Inserting so records on it

INSERT INTO  my_Emplyee

             (EMP_NAME,  SAL)

VALUES('Sukamal jana', 40000),

        ('Manisankar Year', 30000),

        ('Tuhin Shina', 40000),    

        ('Sabgram jit', 30000),  

        ('Subrata Kar', 20000),   

        ('Debojit Ganguli', 20000) 

       

-- Display records

SELECT * FROM my_Emplyee

Output

ID    EMP_NAME          SAL

1     Sukamal jana      40000.00

2     Manisankar Year   30000.00

3     Tuhin Shina       40000.00

4     Sabgram jit       30000.00

5     Subrata Kar       20000.00

6     Debojit Ganguli   20000.00

I am again mentioned that the parameters cannot be passed in views.

Now I am moving to the solution of that by table value function and passing some parameters to get the desired result set.

IF OBJECT_ID (N'fn_EMP_VIEW') IS NOT NULL

   DROP FUNCTION dbo.fn_EMP_VIEW

GO

CREATE FUNCTION dbo.fn_EMP_VIEW

                (@p_Sal DECIMAL(20,2))

RETURNS TABLE

AS RETURN

(

      SELECT *

      FROM   my_Emplyee

      WHERE  SAL>=@p_Sal

)

GO            

             

We want to display the employee details that have salary 40,000 or more than that.

-- Execute it

SELECT *

FROM   dbo.fn_EMP_VIEW(40000)

Output

ID    EMP_NAME          SAL

1     Sukamal jana      40000.00

3     Tuhin Shina       40000.00

Hope you like it.

Posted by: MR. JOYDEEP DAS