Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1537 > unrolled thread

"Parameterized" Views - really that ugly?

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  "Parameterized" Views - really that ugly? Ulrich Achilles <uli.achilles@googlemail.com> - 2013-07-28 11:30 +0200
    Re: "Parameterized" Views - really that ugly? "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2013-07-28 15:28 -0400
      Re: "Parameterized" Views - really that ugly? Ulrich Achilles <uli.achilles@googlemail.com> - 2013-07-30 09:31 +0200

#1537 — "Parameterized" Views - really that ugly?

Hallo,

I am new to SQL Server and I want to migrate an MS Access application 
(Backend, Frontend) to SQL Server. More precise:I want to put the 
backend to SQL Server and use Access as Frontend.
The application deals with courses with a duration of 4 to 8 weeks and 
the students participating in these courses.

Coming from Access I wanted to have an updateable view like:

SELECT CourseID, CourseName, Teacher, CoursePlace, CourseBegin, CourseEnd
FROM courses
WHERE CourseBegin <= @somedate + 6 AND CoursEnd >= @somedate

(@somedate is a monday and the view selects the courses that are actual 
for that week).

Of course I found soon that it is not possible to pass parameters to a 
view in SQL server. As a solution I created a table "parameters" with 
just one row to hold the parameter and joined it to the courses-table:

SELECT CourseID, CourseName, Teacher, courses.CoursePlace, CourseBegin, 
CourseEnd
FROM courses
INNER JOIN
parameters ON courses.CoursePlace = parameters.CoursePlace
WHERE CourseBegin <= parameters.somedate + 6 AND CourseEnd >= 
parameters.somedate
(CoursePlace is the same for all courses)

So the clients set their 'somedate' in the parameters-table and get the 
wanted selection of courses. Of course each client has to set the 
"parameter" each time he runs the view.

I came across that construction in the net, but it was commented as 
horrible, ugly and so forth.

My question: Is it really that bad? And why?

Based on that view and a table with students and a table which records 
the students of each course is a view showing all the students of the 
courses of the selected week. This view gives typically 150 to 300 
students in 10 to 25 courses.
There are at most 5 or 6 users working with the application.

I tried also the recommended solution with a table valued function. With 
Access as frontend this has the severe disadvantage, that I have to use 
pass through queries which are not updateable.

I also tried Access-queries on the linked tables filtering by their 
WHERE-clauses, but they ran significantly slower.

Thank you in advance for any suggestions.

Ulrich Achilles

[toc] | [next] | [standalone]

#1538

Ulrich Achilles wrote:
> (@somedate is a monday and the view selects the courses that are
> actual for that week).
>
> Of course I found soon that it is not possible to pass parameters to a
> view in SQL server.

An Access parameterized view is exactly the same as a simple SQL Server 
stored procedure, except the procedure is not directly updatable. That, 
however. is not a great loss since there is no frontend to SQL Server that 
does everything that Access does for Jet databases. The front end for a SQL 
Server database needs to provide all the functions provided by Access. This 
typically means retrieving read-only data from the database (to avoid 
locking problems) and passing parameter values to a stored procedure that 
performs updates, inserts and deletions as needed.

> As a solution I created a table "parameters" with
> just one row to hold the parameter and joined it to the courses-table:
>
> I came across that construction in the net, but it was commented as
> horrible, ugly and so forth.
>
> My question: Is it really that bad? And why?

Yes, it can be. There are so many things that can cause problems.
Client crashes can leave bad parameter values in the table.
Multiple users can be using each other's incorrect parameter values.
Mainly, it's bad because it's outside of the developer's control (assuming 
that users are entering values into it).

[toc] | [prev] | [next] | [standalone]

#1539

On 07/28/2013 09:28 PM, Bob Barrows wrote:
> Ulrich Achilles wrote:
>> (@somedate is a monday and the view selects the courses that are
>> actual for that week).
>>
>> Of course I found soon that it is not possible to pass parameters to a
>> view in SQL server.
>
> An Access parameterized view is exactly the same as a simple SQL Server
> stored procedure, except the procedure is not directly updatable. That,
> however. is not a great loss since there is no frontend to SQL Server that
> does everything that Access does for Jet databases. The front end for a SQL
> Server database needs to provide all the functions provided by Access. This
> typically means retrieving read-only data from the database (to avoid
> locking problems) and passing parameter values to a stored procedure that
> performs updates, inserts and deletions as needed.
>
>> As a solution I created a table "parameters" with
>> just one row to hold the parameter and joined it to the courses-table:
>>
>> I came across that construction in the net, but it was commented as
>> horrible, ugly and so forth.
>>
>> My question: Is it really that bad? And why?
>
> Yes, it can be. There are so many things that can cause problems.
> Client crashes can leave bad parameter values in the table.
> Multiple users can be using each other's incorrect parameter values.
> Mainly, it's bad because it's outside of the developer's control (assuming
> that users are entering values into it).
>
>
>
Hallo,

thank you very much for your helpful explanations.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web