How to pass parameters to a SQL view

by | Aug 18, 2015


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.
    [code language=”sql”] CREATE OR REPLACE PACKAGE view_parameters_pkg AS
    FUNCTION get_example RETURN INT;
    PROCEDURE set_example(i_example INT);
    END view_parameters_pkg;
    [/code]
  2. The variable itself lives in the package body.
    [code language=”sql”] 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;
    [/code]
  3. Finally the view definition calls the package.
    [code language=”sql”] CREATE OR REPLACE VIEW example_view AS
    SELECT LEVEL as i
    FROM dual
    CONNECT BY LEVEL <= view_parameters_pkg.get_example()
    ORDER BY LEVEL;
    [/code]

Before we can reference the view we need to set the variable.
[code language=”sql”] exec view_parameters_pkg.set_example(10);
select count(*) FROM example_view;
exec view_parameters_pkg.set_example(2);
select * from example_view;
[/code] 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.
    [code language=”sql”] CREATE TABLE [view_parameters] ([spid] INT NOT NULL
    ,[key] VARCHAR(50) NOT NULL
    ,[value] VARCHAR(500)
    ,PRIMARY KEY ([spid],[key])
    );
    [/code]
  2. The view references the table. (Thanks to Aaron Bertrand for the numbers query)
    [code language=”sql”] 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]);
    [/code]

Now we just need to set the value and the view reacts appropriately.
[code language=”sql”] 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;
[/code]

(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.

0 Comments
Submit a Comment

Your email address will not be published. Required fields are marked *