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

"Parameterized" Views - really that ugly?

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> (permalink)
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

Show key headers only | 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