parameters for view in SQL server

Question

In SQL Server 2005, I have a complex view that needs to specify additional conditions on certain relations. These conditions are not known at the time of view creation. Here's a greatly simplified version.

SELECT fields FROM table1 
LEFT JOIN table2 ON ((table1.pid = table2.fid) AND (table2.condition1 = @runtimecondition));
LEFT JOIN table3 ON ....
LEFT JOIN table4 ON .... 
LEFT JOIN table5 ON ....

Dynamic SQL to directly access the tables and do the joins is not an option for me because of 3rd party constraints (this is an integration and they want to have a single point of access for me code, preferably a view -- rather than grant access to a variety of tables). Can this be done with a view? Do I have to use a stored procedure? Is this a problem that could be addressed by a Table Valued Function?

You have to use something more powerful than a view, such as a TVF. Views cannot be parameterized. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 8, 2013 at 13:47

Oleksandr Fedorenko · Accepted Answer · 2013-05-08 13:19:19Z

5

You can use Inline Table-Valued function

CREATE FUNCTION dbo.Condition
 (
  @condition1 int,
  @condition2 int,
  )
RETURNS TABLE
AS
RETURN (
        SELECT *
        FROM table1 t 
          LEFT JOIN table2 t2 ON t.pid = t2.fid 
            AND t2.condition1 = ISNULL(@condition1, t2.condition1)
          LEFT JOIN table3 t3 ON t.pid = t3.fid
            AND t3.condition1 = ISNULL(@condition2, t3.condition1)
          LEFT JOIN table4 ON .... 
          LEFT JOIN table5 ON ....
        )

Demo on SQLFiddle

For improving performance use this indexes:

CREATE INDEX x ON dbo.table1(pid)
CREATE INDEX x ON dbo.table2(condition1) INCLUDE(fid, pid)
CREATE INDEX x ON dbo.table3(condition1) INCLUDE(fid, pid)

Plan Diagram(on example of the three tables)

enter image description here

edited May 8, 2013 at 13:19

answered May 5, 2013 at 6:53

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mvp · Accepted Answer · 2013-05-05 00:28:38Z

1

You can expose field(s) of interest to list of produced fields:

CREATE VIEW myview AS
SELECT fields,
     table2.condition1 AS condition1
FROM table1 
LEFT JOIN table2 ON (table1.pid = table2.fid);
LEFT JOIN table3 ON ....
LEFT JOIN table4 ON .... 
LEFT JOIN table5 ON ....

This lets VIEW users impose conditions when they use your view, something like this:

SELECT * FROM myview
WHERE condition1 = @runtimecondition

answered May 5, 2013 at 0:28

mvp

118k15 gold badges132 silver badges155 bronze badges

2 Comments

Oleksandr Fedorenko Over a year ago

But in this case semantic of queries will be different.sqlfiddle.com/#!3/d9824/2

scotru Over a year ago

Yes--that's exactly the problem I was running into. The queries don't seem to be logically equivalent--I get different result sets.

Serge · Accepted Answer · 2013-05-08 13:44:24Z

0

This one could be less efficient than the function table (but I don't think it will).

View defined as :

SELECT
    fields
    , table2.fid AS t2fid
    , table2.condition1 AS t2condition1 
FROM table1 
LEFT JOIN table2 ON (table1.pid = table2.fid)
LEFT JOIN table3 ON ....
LEFT JOIN table4 ON .... 
LEFT JOIN table5 ON ....

View called like:

SELECTT
    fields
FROM dbo.MyView
WHERE (
    t2fid IS NULL
    OR
    t2condition1 = @runtimecondition
)

answered May 8, 2013 at 13:44

Serge

6,7425 gold badges33 silver badges58 bronze badges

Collectives™ on Stack Overflow

parameters for view in SQL server

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related