Passing a parameter to views

[ngoodearl]

Hi,

I have upsized an access db to a sequel 2000 db. The db is used to perform a series of calculations to get a result. The user inputs the required parameters into a form and the data entered is used in the calculations.

At the moment we have a stored procedure that performs the calculations. This contains 22 queries. The remaining queries are stored as views (over 30). The problem we have is that one parameter is set in a view outside the stored procedure. In testing we had to manually change the sql to reference the correct record we were processing. The problem now is because a view cannot pass parameters it is not retrieving the parameter needed from the form for use in the calculations. The sql for the view is

SELECT Obligor_ID, LEFT(SIC_ID, 2) + '00000' AS SIC_ID_Main, Weighting
FROM dbo.tblObligorSIC
WHERE (Obligor_ID = 106)

The WHERE clause is the issue.

In an attempt to work around this we thought of changing this view to a table valued function. The issue then arose of how the other views would look up the output from the function (the obligor ID) as one view relies on another view and so on. We also thought of changing all the views to functions but this would create alot of problems with the code that would require testing and at the moment due to constraints this isn' t feasible and we're not sure if that would resolve the issue anyway.

Is there a way of taking a parameter entered in an asp form to change a view?

A suggestions would be most welcomed

Nathan