interactive queries within a view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

interactive queries within a view

Author

Topic

qwerty1234
Starting Member

13 Posts

Posted - 2011-12-06 : 10:12:13

I want to create a view with the following code so that it asks the user to enter a branch id when they run the view. however atm it asks for the branch id at the creation of the view therefore the branch id id set each time the view is run. is there a way past this?

create view staff_salary_INTERACTIVE
as select branch_id, POSITION, first_name, surname, start_salary, ((start_salary*30)*4) monthly_sal
from staff_table
WHERE BRANCH_ID = &branch_id_value
group by branch_id, POSITION, first_name, surname, start_salary, ((start_salary*30)*4);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-06 : 10:23:59

Create the view without the where clause. Then, when you select from the view, supply the where clause as in:


select
  branch_id,
  POSITION
  -- other columns
from
  staff_salary_INTERACTIVE
where
  branch_id = @branch_id_value

qwerty1234
Starting Member

13 Posts

Posted - 2011-12-06 : 10:32:26

it is not posbile to include an interactive query in a view then?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 10:35:50

quote:
Originally posted by qwerty1234

it is not posbile to include an interactive query in a view then?

thats what procedure is for. then you should be using procedures with parameters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-07 : 03:38:37

quote:
Originally posted by qwerty1234

it is not posbile to include an interactive query in a view then?

No. You should create a stored procedure with input parameter that filters data in a view

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources