Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!newsreader4.netcologne.de!news.netcologne.de!.POSTED!not-for-mail
From: Ulrich Achilles <uli.achilles@googlemail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: "Parameterized" Views - really that ugly?
Date: Sun, 28 Jul 2013 11:30:30 +0200
Organization: news.netcologne.de
Lines: 55
Distribution: world
Message-ID: <kt2obm$t1d$1@newsreader4.netcologne.de>
NNTP-Posting-Host: xdsl-84-44-145-92.netcologne.de
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: newsreader4.netcologne.de 1375003830 29741 84.44.145.92 (28 Jul 2013 09:30:30 GMT)
X-Complaints-To: abuse@netcologne.de
NNTP-Posting-Date: Sun, 28 Jul 2013 09:30:30 +0000 (UTC)
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130510 Thunderbird/17.0.6
Xref: csiph.com comp.databases.ms-sqlserver:1537

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