You must Sign In to post a response.
  • Category: SQL Server

    How to passing Parameter in view

    How to Pass Parameter(that means Where Condition in renTime)in View table.

    (EX: My ViewTable)
    (How to set Where Condition )Its Possible Or Not...

    CREATE VIEW [dbo].[ViewTimeSheet]
    AS
    SELECT TOP (100) PERCENT dbo.tblWorkLog.EmployeeID, dbo.tblEmployee.Name, CONVERT(varchar(15), dbo.tblWorkLog.WorkDate, 111) AS WorkDate,
    SUM(dbo.tblWorkLog.WorkHour) AS TotalWorkHours
    FROM dbo.tblEmployee INNER JOIN
    dbo.tblWorkLog ON dbo.tblEmployee.ID = dbo.tblWorkLog.EmployeeID

    GROUP BY dbo.tblEmployee.Name, dbo.tblWorkLog.WorkDate, dbo.tblWorkLog.EmployeeID
    ORDER BY WorkDate DESC
  • #749579
    hi
    Kiruba

    try this code working Good


    Alter View Test
    as
    SElect * from [Users] where UserID=2

    Select * from Test


    Name : Dotnet Developer-2015
    Email Id : kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #749590
    The approach to pass where condition at runtime to a view is not possible. You can use stored procedure instead to get this done.

    View is for static select statement only. For any additional manipulations SQL Server has the Option "Stored Procedure".

    Please mark this as Answer, if this helps

    Regards,
    Alwyn Duraisingh.M 
    << Database Administrator >>
    Jesus saves! The rest of us better make backups...

  • #749594
    We cant pass the Parameter in view. For passing parameters it should be either procedure or User defined functions.

  • #749603
    Hey,
    you cant pass parameter to a view. Its like a virtual table.If you want to make query parameterized then it should be a stored procedure/function and not a view.
    For SSRS, best would be to use stored procedure as backend with your required parameter.
    Also a possible solution would be to implement a stored function, like:
    CREATE FUNCTION v_emp (@pintEno INT)
    RETURNS TABLE
    AS
    RETURN
    SELECT * FROM emp WHERE emp_id=@pintEno;
    The benefit of this is, This allows you to use it as a normal view, with
    SELECT * FROM v_emp(10)

    Thanks,
    Ashutosh Jha
    http://tricksroad.com

  • #749629
    Looks like you are using the view in wrong place. If it dynamic then why you require view? It should be procedure. View will be created only to get static data form multiple table or single table based on the condition. View cannot be used for dynamic data.

    Regards,
    Asheej T K

  • #749678
    Hai Kirubaharan,
    Creating parameterized view is nothing but creating a function by passing the parameter.
    So in your case, you can write the function as:

    CREATE Function [dbo].[ViewTimeSheet](@empid int)
    AS Returns Table
    SELECT TOP (100) PERCENT dbo.tblWorkLog.EmployeeID, dbo.tblEmployee.Name, CONVERT(varchar(15), dbo.tblWorkLog.WorkDate, 111) AS WorkDate,
    SUM(dbo.tblWorkLog.WorkHour) AS TotalWorkHours
    FROM dbo.tblEmployee INNER JOIN
    dbo.tblWorkLog ON dbo.tblEmployee.ID = dbo.tblWorkLog.EmployeeID
    WHERE dbo.tblEmployee.ID=@empid
    GROUP BY dbo.tblEmployee.Name, dbo.tblWorkLog.WorkDate, dbo.tblWorkLog.EmployeeID
    ORDER BY WorkDate DESC

    And now you can call this function as:

    SELECT * FROM dbo.ViewTimeSheet(1)

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com


  • Sign In to post your comments