Stored Procedures vs In-Line SQL

[SeanK]

I'm someone who has written a lot of SQL, but always in-line SQL and I'd like to get more involved with the use of stored procedures. I some general questions:

What is the advantage of using stored procedures over in-line SQL?
Are there any disadvantages of using stored procedures over in-line SQL?
Are there instances where you should always use a stored procedure?
Are there instances where you should skip the stored procedure and just code your query in-line?

Thanks.

[JPicasso]

I"m just getting into them myself and I like em.

I still have some in-line SQL calls alongside my SP calls, but the more I work with SP, the more I'm using them.

1. They are faster.
2. They are easier for me to code around because you treat them like a separate procedure.
3. Multiple programs accessing the same data can use them and produce the same results each time. You won't have one program forgetting to put in a required field.
4. If you change the database structure, it's nice to be able to make the majority of your data call changes right there. (you still may have to make changes to the programs, but not as many)

My opinion.

The database forum has a lot of good info regarding SP. browse some of those posts.

[RobDog888]

Similar to what JPicasso posted.

1. They are faster and more secure.
2. They take longer/harder to debug.
3. Whenever security is a factor or performance.
4. ? If you want to be exposed to possible t-sql query injection hacks.

HTH

[szlamany]

They can actually be a lot more than a query - with declared variables and IF statements and WHILE loops, they can pretty much be little programs - written in T-SQL language.

Very powerful.

We appreciate them for many reasons. For one thing, you do not have to give users access to tables - just the SPROCS themselves. This is a better security model than allowing users access to INSERT/UPDATE/DELETE table data.

They separate the SQL from the VB - meaning that you can change the SPROC, compile it back into the DB, and never have to change the application executable.

They are parameterized - you pass the arguments you want to use in the WHERE clause, for instance, as parameters to the SPROC.

Recently we took a old mainframe payroll calculation routine - many, many pages of BASIC code - I/O - array building. Copied that into a SPROC written in T-SQL - now it's a couple of dozen SELECT's and INSERT's into table variables and it took less than a day to develop, compared to the weeks of time it took to develop and test the BASIC code 20 years ago.

If you are going to go SPROC - then never, ever use IN-LINE SQL. That will give you the ability to take all table access away from users - only SPROC access.

Check out this post I made last year...

SPROC info

[plenderj]

I think given the speed of computers these days, the fact that SP's easily help defend against SQL injection attacks would put their security-related features above that of performance...

[szlamany]

I think given the speed of computers these days, the fact that SP's easily help defend against SQL injection attacks would put their security-related features above that of performance...

I agree - I've read that the "pre-compiled" aspect of SPROCS is not longer an asset in SQL SERVER 2000. The query optimizer can cache in-line SQL statement use as well as SPROC use.

That doesn't mean in future versions of SQL that the SPROC won't once again rise to a new level of optimization that in-line SQL use cannot achieve.

SPROCS really are the best practice method of SQL use - true black-box functionality - can really hit the ACID test easily. It's hard to hand-shake data back and forth from client to server and make the ACID test (although I'm those that do it will argue that point).

Can anyone point out a disadvantage to SPROC usage - as the second question asked?

[plenderj]

Well, its a little harder to debug your application. Also, it means a little extra work when replicating the DB onto different servers because you've to copy the SPs too - but this is nearly completely trivial :shrug:

[szlamany]

Well, its a little harder to debug your application. Also, it means a little extra work when replicating the DB onto different servers because you've to copy the SPs too - but this is nearly completely trivial :shrug:

I will agree - the debugging can be harder - but developing in QUERY ANALYZER is a piece of cake - very easy to run and re-run queries for testing purposes (especially with BEGIN TRAN/ROLLBACK at top and bottom).

We happen to use a very standard SPROC to VB parameter set - actually the first 9 parameters are identical in every SPROC we have (over 700 now!).

We also SCRIPT everything, so the copy from one DB to another has become a standard release of a new SPROC. We've even automated "executing" new SPROCS into production databases with .BAT scripts that we create with a little VB program.

Never using ENTERPRISE MANAGER for anything makes the SPROC life a bit easier!

[plenderj]

We happen to use a very standard SPROC to VB parameter set - actually the first 9 parameters are identical in every SPROC we have (over 700 now!).

You'll probably like the features in SQL 2005 to include .NET assemblies into the DBs so that you can fudge your own SQL Stored Procedure Overloading

[szlamany]

You'll probably like the features in SQL 2005 to include .NET assemblies into the DBs so that you can fudge your own SQL Stored Procedure Overloading

We started a major Digital VAX/BASIC conversion almost 4 years ago now. The customers all wanted MS - and SQL 2000 was finally strong enough for tables with 3 million rows - database that had concurrent user counts in the hundreds...

Unfortunately VB.Net was so new - and we had no PC experience anyway - that we were afraid to use .Net (and of course ADO.Net).

We are nearly done with the conversion - two business lines are converted (school district and town hall) - one is getting ready for beta testing in March - live in April (labor union).

We've kept the VB6 client so thin - no business logic - even reads the form positions of textboxes and flexgrids from the database - that converting to .Net should be a piece of cake.

We truly look forward to it.

[plenderj]

I like it. You've taken n-tier designs really to heart! ADO.NET provides some very cool funtionality. And it also includes a dedicated client for SQL Server (plus a downloadable one for Oracle).
So all communications between your code and the SQL server should see a huge performance increase when switching to ADO.NET using the System.Data.SQLClient namespace

[SeanK]

Well, these are the kind of replies I was hoping for. Thanks everyone.

[Hack]

I understand and completely support the use, in general, of stored procedures over inline SQL, but there are times when inline SQL fits the bill nicely. An example would be a query that you would never need to change, like obtaining the next sequential record id. I fail to see the need to put something like that in a stored procedure.

[szlamany]

I understand and completely support the use, in general, of stored procedures over inline SQL, but there are times when inline SQL fits the bill nicely. An example would be a query that you would never need to change, like obtaining the next sequential record id. I fail to see the need to put something like that in a stored procedure.

Then you are giving direct table access to the user. If using windows authentication, then you are giving the username access to the table.

From there anyone with EXCEL can connect to the database and look at any record.

We develop HIPAA certified health processing software - that would be a breach of security. And since Windows security is the only security model permitted in our industry that's a fact (and also the only model that makes sense for use!).

So there is the need for a SPROC in the situation you just described.

[Hack]

Then you are giving direct table access to the user. If using windows authentication, then you are giving the username access to the table.

From there anyone with EXCEL can connect to the database and look at any record.

How am I doing that? (We don't use windows authentication by the way).

[szlamany]

How am I doing that? (We don't use windows authentication by the way).

So do your users know their SQL Authentication username and password?

[Hack]

So do your users know their SQL Authentication username and password?

I've only been here for three months, but from what I understand, the username and password is encapsulated (hard-coded) within a compiled Dll that gets installed with our product once it has been purchased.

[szlamany]

I've only been here for three months, but from what I understand, the username and password is encapsulated (hard-coded) within a compiled Dll that gets installed with our product once it has been purchased.

As long as no customer ever finds out the USERNAME or PASSWORD, then no one can get into EXCEL and look at the data.

If you were allowing the user to CONNECT by entering a SQL username and password, then that same un/pw could be used from any MS product (or other products!) to access the data - since the table access is given to that un/pw.

BTW - SQL authentication is not good practice - MS has been recommending that users authenticate through WINDOWS Authentication - SQL username and password table is hackable...

[Hack]

BTW - SQL authentication is not good practice - MS has been recommending that users authenticate through WINDOWS Authentication - SQL username and password table is hackable...

I completely agree. In fact, this is the first, and only, place I've ever been that used it. Every place else I've been has used Windows authentication.

So, are you saying that using inline SQL for certain tasks, the parameters of which will never change, really isn't causing a potential security problem?

'Cause if it is, then that is certainly a solid reason for moving even these small one and two line tidbits to a stored procedure.

[szlamany]

The security issue is the SQL vs Windows authentication. Since they use SQL authentication - and "hide" the username and password from everyone - then no one can access the data - since they don't know the un/pw.

Since we use Windows Authentication, then if we gave the domain username access to table data, then they could use any tool available to mess with the data - that's our security concern.

When we started this project 3 years ago - migrating software from Digital/Vax mainframe type computers to VB/SQL, we started with in-line SQL.

Once we saw that users could touch data with EXCEL - we realized that we needed to remove all INSERT/SELECT/UPDATE/DELETE rights from all tables. That meant we had to go 100% SPROC and only give EXEC rights to SPROCS to the users.

Searching for every last little in-line SQL was a pain - that's for sure!

Personally, now that we are 100% SPROC we really enjoy the advantage of changing any SQL at the server - and not having to release new executables - for simply query changes.

[Hack]

...we really enjoy the advantage of changing any SQL at the server - and not having to release new executables - for simply query changes.

It is absolutely and physically impossible for me to agree with this statement more. In my mind, that one of the best reasons to go to stored procedures. In the past, speed was also a good reason to go to stored procedures, but given the processiong power of today's desktop/laptop computers, that advantage is almost moot. However, (and if I'm beating a "dead horse" then I apologize up front), inline SQL is not something that should be discounted simply because it is not a stored procedure.

When it comes to querys involving Most INSERTs/Most SELECT/Most UPDATEs/Most DELETEs then I completely agree with, and advocate the use of, stored procedures.

Looking back over all of the posts in this thread, I discover that I've not given an actual example of what I'm referring to. So, with that in mind, here a couple of things that I do not, can not, and unless someone can give me a very solid reason, will not, believe should be placed into a stored procedure.

sSql = "SELECT MAX(idfieldname) AS nextidnum FROM table"
set adors = dbconn.Execute(sSql)
variable = adors.fields("nextidnum").value + 1
(close, set to nothing, etc)

sSql = "SELECT COUNT(*) FROM table"
or
sSql = "SELECT COUNT(fieldname) FROM table"

If your program needs the information that these queries will return, it will always need it. The queries will never have to be modified. I'm sure there are other examples, but these are two inline SQL queries that pop immediately to mind.

For most instances, stored procedures should be used, but they are not the exclusive determining factor of a well built or secure application. Inline SQL does have its purpose.

In the words of Samuel Butler: "Half the vices which the world condemns most loudly have seeds of good in them and require moderated use rather than total abstinence."

[szlamany]

sSql = "SELECT MAX(idfieldname) AS nextidnum FROM table"
set adors = dbconn.Execute(sSql)
variable = adors.fields("nextidnum").value + 1
(close, set to nothing, etc)

sSql = "SELECT COUNT(*) FROM table"
or
sSql = "SELECT COUNT(fieldname) FROM table"

If your program needs the information that these queries will return, it will always need it. The queries will never have to be modified. I'm sure there are other examples, but these are two inline SQL queries that pop immediately to mind.

Do we all agree up front that having these in-line SQL queries - mixed with WINDOWS authentication - gives the user access to table data? We happen to be extremely concerned with that - since HIPAA government regulations make us track "every" inquiry of data from a table.

But besides that:

Having that logic in the client side, and no other business logic (assuming that most logic is in SPROCS) is kind of breaking the n-tier design (or 2-tier - I don't want to bring this into a tier argument!).

Since the developer is not using an IDENTITY column, that means that there is more to the QUERY than just COUNT(*) from a table. We happen to use a TRANSACTION NUMBER in our FINANCIAL package - but it's FISCAL YEAR specific.

So with a PRIMARY KEY of FISCAL YEAR+TRANSACTION NUMBER, we would require a WHERE clause on that QUERY. As soon as it jumps from a simply "table count" to a WHERE condition, the possibility of future logic changes (maybe ledger types start at 1 and budget types start at 100000) means that it belongs in a SPROC, in my opinion.

And this is really all just opinion at this level.

We have a function called GETCONF_F - we do call that in-line - so in that regard I have broken my own rule.

    rs.Open "SELECT dbo.GetConf('" & ConfItem & "') as ConfData", gCn
    GetConf = rs!ConfData & ""

But at least I didn't give TABLE access to the CONFIGURATION table.

We happen to add a lot of new security features to various queries as time goes on - only need access to this school year, or can't see office personnel. I've been surprised at how some of the simplest queries have required minor adjustments.

But that happens to be the nature of our product - we fully customize the product for every customer - and also make those customizations availabe to all existing customers.

[plenderj]

I understand and completely support the use, in general, of stored procedures over inline SQL, but there are times when inline SQL fits the bill nicely. An example would be a query that you would never need to change, like obtaining the next sequential record id. I fail to see the need to put something like that in a stored procedure.

Well if it never changes, then surely it would be good to benefit from the pre-execution plan that SQL would create for the SP?

[JPicasso]

I understand and completely support the use, in general, of stored procedures over inline SQL, but there are times when inline SQL fits the bill nicely. An example would be a query that you would never need to change, like obtaining the next sequential record id. I fail to see the need to put something like that in a stored procedure.

This was one of the reasons we put that into a stored procedure. So that multiple users wouldn't try and use the same next recordID on their new orders/entries.
We built the "autonumber" feature into the insert statment itself and the NextAvailableRecordID is called during the INSERT statemnt, via SP.

If you were talking about something else, then nevermind.

[divined]

I`ve followed this thread from the start. I would like to ask a question pertaining to the application I`m developing! I`m using inline SQL statements and SQL authentication (MySQL). How would Windows authentication render my app more secure? Can it be used under MySQL?

[szlamany]

I`ve followed this thread from the start. I would like to ask a question pertaining to the application I`m developing! I`m using inline SQL statements and SQL authentication (MySQL). How would Windows authentication render my app more secure? Can it be used under MySQL?

I cannot answer anything specific to MySQL - as we are a MS SQL shop here.

SQL authentication asks the user for username/password when the application runs - this is generally recognized as a step that is not required for authentication. For example, getting into OUTLOOK does not ask for a username/pw.

The username/pw table in SQL is hackable - it's not the same level of security that is afforded by CTRL/ALT/DELETE logon at the operating system level.

Here's a google link to a search - if you want to read further - lots of garbage links here also...

Google Windows Authentication White Papers

[plenderj]

Yes I personally only use Windows Authentication.

On another note, if one uses Stored Procedures a lot, then you'd benefit greatly from being able to develop them in .NET managed code and store it as an assembly inside your SQL 2005 DB