Friday, 25 May 2012

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
  

9 comments:

  1. A view is a virtual table so you can use your parameters in the where clause.

    SELECT *
    FROM vwEmployee
    WHERE Salary > @Sal;

    ReplyDelete
    Replies
    1. Thanks "Artoo".
      Here i am taking about passing parameters into the view NOT queering the view in WHERE clauses.

      Delete
  2. Great explanation.Thanks for your post.

    ReplyDelete
  3. Hello Joydeep sir, can we join the SP result set with Table

    ReplyDelete
  4. Thanks!!!! A very helpful way we can use a day...

    ReplyDelete
  5. Hey, hopefully someone is going to read this.

    I have the some problem right now. The problem is, I can´t set functions or tables on the DB - because it´s a live system.
    But I need to use the parameters ... Any ideas?

    Thanks

    ReplyDelete
    Replies
    1. "..I can´t set functions or tables on the DB - because it´s a live system...." Can you explain more? Is it that you do not have write access to the db to create obects? You can always use ADO.Net in a console app.

      Delete