Very Computer

I'm currently building my first web site using ASP.NET and SQL Server 2000.
The model I'm following has all SQL statements stored as stored procs, even
the little itty bitty ones like those for deleting rows from a table using a
row id. Do people actually build systems like this or this a bit OTT?
Doesn't this just add more complexity?

TIA

Having all the database logic in the database is actually quite beneficial,
both performance-wise and maintenance-wise.  The front-end code ideally just
sends data and requests to the "black box" stored procedure, and shouldn't
have any SQL exposed at all, IMHO -- regardless of how trivial.  The problem
with defining a SQL statement as "itty bitty" and determining its placement
on that criteria alone is that you might think that's itty bitty but the guy
in the next cube might not.  I think it makes the most sense to isolate the
query logic to one place...

--
www.aspfaq.com / www.perfhound.com

Quote:> I'm currently building my first web site using ASP.NET and SQL Server
2000.
> The model I'm following has all SQL statements stored as stored procs,
even
> the little itty bitty ones like those for deleting rows from a table using
a
> row id. Do people actually build systems like this or this a bit OTT?
> Doesn't this just add more complexity?

> TIA

Don't embed SQL - use stored procedures where possible - you are doing the
correct thing - even if the proc has a singleton select or insert - thats
fine.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]

Quote:> I'm currently building my first web site using ASP.NET and SQL Server
2000.
> The model I'm following has all SQL statements stored as stored procs,
even
> the little itty bitty ones like those for deleting rows from a table using
a
> row id. Do people actually build systems like this or this a bit OTT?
> Doesn't this just add more complexity?

> TIA

I've just started out on a project like this where I put all SQL
statments into sql2k s.procs

Heres an example of my basic "select" proc

I was even thinking of extending it for a tablename but not sure.

CREATE PROCEDURE usp_FindUserName


fields if non supplied
AS
SET NOCOUNT ON

-- because where adding the user name to a string we need to check for
the single quote character.
-- if we find it we need to double it so Miles O'Brian becomes Miles
O''Brian use the UDF SQLstring.


-- Create the SQL string to have custom fields as well.


--Execute the above sql string

On Tue, 30 Jul 2002 10:04:42 +0100, "Tony Rogerson"

Hi Harag,

Sort of defeats the point of using a stored procedure, unless you are
writing a dynamic front end that is.

You should really write a stored procedure per operation you want to do...

CREATE PROCEDURE usp_FindUserName

as
begin
    set nocount on

    select UserID, UserName
    from ...
    where ....

end

You are also opening yourself up to somebody implanting sql, if the front
end had access to the db or the ASP got hacked, what would stop this...

That would implant the DROP DATABASE in your SQL and run it (assuming the
user running had the access rights).

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]

A valid point. My basic thinking... remember I'm a newbie... was that
this procedure would be used is several parts of the ASP app but the
different parts would require different fields of information. I'm
still designing the app so not sure on how useful this type of
procedure will be. But maybe I should rethink the below proc and
design several procs ie. LoginUser, UserExists, GetUserAddress,
GetUserAccountStatus - Would this be the more logic way of doing it
then? ( all the above 4 procs would be searched on username)

At the moment I'm the only developer on the project. So I know that I
won't "Drop Database", but the asp hacker might.

Thanks for pointing this out... It all helps when designing the site
and the best way to do things.

Alan.

On Tue, 30 Jul 2002 16:01:13 +0100, "Tony Rogerson"