Select Page

Have you every wanted to pass a parameter to a view? It’s a standard request that can be a bit difficult to implement.

The simplest thing to do, when your tools support it, is to return a result set from a stored procedure. Microsoft SQL Server has supported this forever, and Oracle added PIPELINED functions more than a decade ago in 9i. Unfortunately, there are more than a few tools still out there that aren’t prepared to accept a result set from a stored procedure or function.

In these cases you can inject your parameter into the database somewhere where the view can read it.

Oracle

In Oracle PL/SQL the correct thing to do is to use a package variable. Package variables give you session scope automatically.

  1. First we create a package. We declare a simple pair of setter and getter for our variable.
    CREATE OR REPLACE PACKAGE view_parameters_pkg AS
      FUNCTION get_example RETURN INT;
      PROCEDURE set_example(i_example INT);
    END view_parameters_pkg;
    
  2. The variable itself lives in the package body.
    CREATE OR REPLACE PACKAGE BODY view_parameters_pkg AS
      g_example INT;
    
      FUNCTION get_example RETURN INT AS
      BEGIN
        RETURN g_example;
      END get_example;
    
      PROCEDURE set_example(i_example INT) AS
      BEGIN
        g_example := i_example;
      END set_example;
    END view_parameters_pkg;
    
  3. Finally the view definition calls the package.
    CREATE OR REPLACE VIEW example_view AS
        SELECT LEVEL as i
        FROM dual
        CONNECT BY LEVEL <= view_parameters_pkg.get_example()
        ORDER BY LEVEL;
    

Before we can reference the view we need to set the variable.

exec view_parameters_pkg.set_example(10);

select count(*) FROM example_view;

exec view_parameters_pkg.set_example(2);

select * from example_view;

The output is as expected.

 PL/SQL procedure successfully completed.

  COUNT(*)
----------
        10
1 row selected.
 PL/SQL procedure successfully completed.

         I
----------
         1
         2

2 rows selected.

Microsoft SQL Server

In Microsoft SQL Server, the view can be parameterized with a process-keyed table. Just like the oracle example, we set the value before querying the view.

  1. First we create the process-keyed table.
    CREATE TABLE [view_parameters]
    ([spid]   INT NOT NULL
    ,[key]   VARCHAR(50) NOT NULL
    ,[value] VARCHAR(500)
    ,PRIMARY KEY ([spid],[key])
    );
    
  2. The view references the table. (Thanks to Aaron Bertrand for the numbers query)
    CREATE VIEW example_view AS
    WITH e1(n) AS
    (
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    ) -- 10
    ,e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b) -- 10*10
    ,e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
    ,numbers AS (SELECT n = ROW_NUMBER() OVER (ORDER BY n) FROM e3)
      SELECT n
      FROM numbers
      CROSS JOIN view_parameters vp
      WHERE vp.[spid] = @@spid
      AND vp.[key] = 'limit'
      AND n <= convert(int,vp.[value]);
    

Now we just need to set the value and the view reacts appropriately.

MERGE INTO [view_parameters] vp
USING (SELECT @@spid as [spid], 'limit' as [key], 10 as [value]) datum
ON vp.[spid] = datum.[spid] AND vp.[key] = datum.[key]
WHEN MATCHED THEN
  UPDATE SET vp.[value] = datum.[value]
WHEN NOT MATCHED THEN
  INSERT ([spid],[key],[value]) VALUES (datum.[spid],datum.[key],datum.[value]);

SELECT count(*) FROM example_view; 

MERGE INTO [view_parameters] vp
USING (SELECT @@spid as [spid], 'limit' as [key], 2 as [value]) datum
ON vp.[spid] = datum.[spid] AND vp.[key] = datum.[key]
WHEN MATCHED THEN
  UPDATE SET vp.[value] = datum.[value]
WHEN NOT MATCHED THEN
  INSERT ([spid],[key],[value]) VALUES (datum.[spid],datum.[key],datum.[value]);

SELECT * FROM example_view;
(1 row(s) affected)

-----------
10

(1 row(s) affected)

(1 row(s) affected)

n
--------------------
1
2

(2 row(s) affected)

I learned this technique so many years ago I don’t even know who to thank. I hope that you will find it useful.

– Steven

You can read Working spreadsheets into your Business Intelligence strategy or all of Steven’s blogs here.

Find out about courses for SQL Developers here.

%d bloggers like this: