Re: Stored Procedures v Views

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 10/04/04 

Date: Mon, 4 Oct 2004 14:20:48 -0500

Still not enough information. Are all of the parameters required? If so
then it is a no brainer. Build a stored procedure, and any place where you
used parameters in your query in Access, just SQL Server variables. You
cannot have parameters in views, though you can have them in table
functions, which are pretty much views.

The goal here will be to not use any dynamic sql like you used in Access.
The goal will be to write your statement in such a way that you can build a
procedure that has one or more SELECT statements and simply pass in
parameters. For example:

Create procedure selectSomeStuff
(
    @selectSomeStuffCode varchar(10)
) as
begin
        select column1, column2, ..., columnN
         from tablename
        where selectSomeStuffCode = @selectSomeStuffCode
 end

You can do something like:

Create procedure selectSomeStuff
(
    @selectSomeStuffCode varchar(10)
) as
begin
        declare @query varchar(8000)
        set @query = 'select column1, column2, ..., columnN'
        set @query = @query + 'from tablename'
        set @query = @query + 'where selectSomeStuffCode = ''' +
@selectSomeStuffCode + ''''

        exec (@query)
 end
go

If the query is very complex, but frankly, if you have to do this, you are
just about as well off to build the query using your non-data teir
components, since string manipulation is very ugly in T-SQL, since it is
primarily built to do set-based operations.

When I was talking of views, what I meant was that if your query was very
messy, you might encapsulate some of the static parts of the query down into
a view to make it easier to build your dynamic query strings, especially
since it will make your dynamic code generation easier.

Does this make more sense? 

-- 
----------------------------------------------------------------------------
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:DE3BA210-76EB-47B1-8122-EBD9D01AF8C9@microsoft.com...
> 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
> >
> >
> >
Loading