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

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

"Parameterized" Views - really that ugly?

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

"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

csiph-web