Re: Stored Procedures v Views

From: Marek (Marek_at_discussions.microsoft.com)
Date: 10/04/04 

Date: Mon, 4 Oct 2004 11:51:04 -0700

Thanks for the response. What I am trying to do is replicate something I
have already done in my Access DB. I have a very simple form in Access
whereby a user can choose one of 4 options from a set of option buttons -
they are the different categories I have in a table in the DB. They then
choose one of 3 options in a second set of option buttons (similar type of
thing) then finally choose an option from a combo box. The combined choices
they make are then used to create the SQL statement which is then used to
display the results in a listbox. It's fairly straight forward really. The
query draws the data from 4 tables. Works really well in Access. But I now
have to recreate everything in a SQL database with web front end. This is
where I need advice. Do I use a stored proc or a view? I didn't know a View
could contain parameters. If I used a stored proc how do I create the the
query string? Is this best done in the web application that then passes the
parameters over to the Stored Proc? Thats what I am leaning towards.

Marek

"Louis Davidson" wrote:

> Simple. Either, both, or neither :)
>
> Seriously, probably not a view, though you might want to encapsulate the
> static parts of your query into a view. If the criteria is pretty
> straightforward, just use a stored procedure. Stored procedures give you
> real power over what you do, in that you can have multiple statements.
>
> Depending on how complex your criteria is, you might not want to use either.
> Just dynamically build a SQL statement using some other tool (like your VBA
> code does now) and just send dynamic SQL statements. This is rarely the
> best way to go, but it may be, depending on your needs.
>
> You might also want to look at user-defined functions as a possible tool.
>
> If you can be more specific about what your needs are, it will be easier to
> give you an answer. Suffice it to say that 85% of the time (or is it 86%,
> or 87.5%?) stored procedures are the best way to go for a public interface
> into SQL Server when the database is used to support OLTP. Once you have
> this interface, even if your stored procedure is poorly written, you can
> change the underlying structure and as long as the parameters don't change,
> you can rewrite the proc and your user systems don't have to change.
>
> --
> ----------------------------------------------------------------------------
> Louis Davidson - drsql@hotmail.com
> SQL Server MVP
>
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
>
> "Marek" <Marek@discussions.microsoft.com> wrote in message
> news:A62EED87-A532-4769-AB48-D21882A2438B@microsoft.com...
> > Hi,
> >
> > New to SQL 2000 - previously an Access person - pretty good at it too!
> >
> > My question is about using a stored procedure or Views to return a
> > recordset. I have an Access database that I am converting in SQL. I have
> > some VBA code that generates a whole load of rows and displays the results
> in
> > a data*** form. The code dynamically generates a SQL statement in VBA,
> > creates a temporary query using DAO and bingo. Creating the SQL statement
> > was tricky - it depends on what options the user has selected in terms of
> > what they want to see. It effectively a little querying tool. But it
> works
> > very nicely in Access and now I need to replicate the same thing in SQL.
> Not
> > sure how to go about this - use a View or Stored Procedure - from what I
> can
> > gather it would be a stored procedure but I am not sure where to start.
> >
> > --
> > Many thanks in advance for any assistance
>
>
>