How to create parametrized view in SQL Server?

Question

I have two views like below

create view penord1_view 
as
    select 
        ser, docno, pcode, barcode, pname, unid, unit, qty, rate 
    from 
        sale 
    where    
        tc = 'O01' and docdt > @xdate1 and docdt < @xdate2

    union 

    select 
        sser as ser, sdocno as docno, pcode, barcode, pname, unid, unit, qty, rate 
    from     
        sale 
    where 
        tc = 'P01' and docdt > @xdate1 and docdt < @xdate2

and the second one is

create view penord_view 
as
    select 
        ser, docno, pcode, barcode, pname, unid, unit, rate, 
        SUM(qty) AS qty 
    from 
        penord1_view 
    group by 
        ser, docno, pcode, barcode, pname, unid, unit, rate

The problem is that I cannot pass @xdate1 parameter in the first view.

Please suggest how to do this in a view or in some other way in SQL Server

Why pass any parameters? You could return the docdt field from the first view and add WHERE statement in the second view — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 27, 2016 at 9:24
docdt cannot be returned from the first view because i cannot sum using docdt — MANMEET SINGH
– MANMEET SINGH, Commented Sep 27, 2016 at 9:47
even i cannot use function in view ....can i use a scaler function in view ? — MANMEET SINGH
– MANMEET SINGH, Commented Sep 27, 2016 at 9:50
You can use a table-valued function in a view, that's their main usage. What did you try? And why do you want parameters in the first place? Just expose docdt, filter by it and then don't include it in GROUP BY or SUM — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 27, 2016 at 10:20

marc_s · Accepted Answer · 2016-09-27 09:21:34Z

1

A view has no parameters by definition. You can see it as a saved query. If you would like to use parameters you should look into using stored procedures.

edited Sep 27, 2016 at 9:21

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 27, 2016 at 9:17

Tom

7475 silver badges17 bronze badges

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

2 Comments

Panagiotis Kanavos Over a year ago

Functions, not stored procedures. Functions can be used in queries while stored procedures can't

Ahmed Saeed Over a year ago

you may also use a table valued function which will look like a paramete view.

Collectives™ on Stack Overflow

How to create parametrized view in SQL Server?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related