No more stored procedures
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Is your company being sold or merged with another? Regardless, I don't see how avoiding stored procedures makes the code more portable to other databases. You are still writing TSQL which is not the same as other SQL implementations. I have done a side by side implementation of packaged software against both Oracle and SQL Server. We specifically used stored procedures in order to keep a single set of ASP code as the SQL was much different between the 2 databases. That's a rather strange request...
-
And just use one sql call: recordset.open( "insert data into table; select @@SCOPE_IDENTITY" )
hmmm... maybe the semi-colon is not general across databases. Perhaps detect if SQL Server before using it otherwise you may have to use it. Also... is @@SCOPE_IDENTITY database general? If not, you may have to have 'dialects' for different databases... e.g. http://coldfusion.sys-con.com/read/43794.htm
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Miszou wrote:
asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do.
If that is his concern, you could look at using NHibernate[^] or ActiveRecord[^] from Castle[^], this will allow you to be database agnostic.
- Nick Parker Microsoft MVP - Visual C#
My Blog | My Articles -
I guess anything is possible, I've never seen it be the case in practice, my apps are business apps and make *extreme* use of the power of the database engine itself and I've tested and timed and profiled with all manner of different queries and back end databases when I was researching going to a data access layer a couple of years ago and I could never find a perceptible difference between dynamic and stored procedure. Something which a lot of people said would be the case despite the common belief that stored procedures are always faster. I'm kinda intense on this issue :) and any other issue involving software development where people have beliefs that aren't validateable in the real world. I think I'm going to publish a "Heretics guide to software development" explaining why Frames are not necessarily a bad thing in web applications, why stored procedures are a bad thing in commercial application design etc etc. I can only imagine the grief people would try to give me! :)
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
-
Where I work, stored procedures are not used, but for a different reason. Stored procedures cannot be checked into a source control system and maintained under version control. On very big projects with lots and lots of programmers and multiple versions of software that are backwardly compatible, version control is a must.
Most major version control systems such as SourceSafe, CVS and PVS allow for version control of the underlying script. In fact, SQL Server 2005 is tightly integrated on a project basis with SourceSafe out of the box. So I do not understand this as a reason, but hey to each his own.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Except for some special cases there is no sense in using application generated unprepared queries. I always use prepared statements such as stored procedures and user-defined functions. It is more important in case with commercial projects to use prepared compiled statements, if you want to speed up your program. It is easy to transfer a stored procedure to a target server. I cannot see any reason for abandoning database logic except for some cases such as need to switch to a different database server platform in the nearest future. In case you use a complex web project that has its own engine, the database server must handle almost all aspects of your site through stored procedures and functions, that is: user authorization, gathering information about page components in case they are dynamically loaded depending on which address you query (it may be the same file with a different page id or address) and the info about them is stored in a database, page title, menus, navigation bars and other commonly used parts of a web page.
-
That's the dumbest thing I've ever heard.
CodeProject: 'I mean where else would you rather be pissed off?' - Jeremy Falcon
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Your bosses idea that possible changes of the back end databases is a reason to go with in line SQL is flawed. This is all the more reason to use stored procedures. What if you use a function in your query that does not exist on the other db platform? This is easy to get around if you use SPs but if all your SQL is compiled guess what.... Is your boss a developer?
Jim Blexrude Senior Software Developer/Consultant
-
hmmm... maybe the semi-colon is not general across databases. Perhaps detect if SQL Server before using it otherwise you may have to use it. Also... is @@SCOPE_IDENTITY database general? If not, you may have to have 'dialects' for different databases... e.g. http://coldfusion.sys-con.com/read/43794.htm
@@SCOPE_IDENTITY doesn't even work on SQL Server. It should be SCOPE_IDENTITY(). (It's a system function, not a global variable.)
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM users WHERE clue IS NOT NULL GO
(0 row(s) affected)
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Excuse to the others but indeed this IS the dumbest thing I ever heard. Please post your supervisors email for us and we will take care of this for you.... (giggle)
-
James R. Twine wrote:
Not really - we have had little utilities like strings for quite some time now. If you have enough permissions to launch the application, you have enough to dump the binary into an editor and/or get the strings out of it. Unless you encode the strings in some manner, they are in the binary in plaintext.
I can't tell you the number of clients over the years that have messed with databases that we include with our software. They see it, have a little experience and want to mess with it. Stored procedures are easily messed with by end users, dynamic sql isn't. The number one security threat for commercial software developers is protecting users from themselves, external threats are a distant second.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
I think what is missed in the whole discussion so far is to criticise the thinking that developers can program for unknown and unseen circumstances. This is like saying 'make something but I'll give you the requirements later'. If the company is going to re-tool at some point, make sure you architect your business logic into a middle tier so that its less effort to swap out your data layer. I worked at a company that moved from MS SQL over to Oracle. It was very easy to see that we put way to much logic in the stored procedures. Either way, if the company decides to swap out one technology for another there is going to be significant cost. There is no avoiding it.
Joel Palmer Application Developer
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
-
Most major version control systems such as SourceSafe, CVS and PVS allow for version control of the underlying script. In fact, SQL Server 2005 is tightly integrated on a project basis with SourceSafe out of the box. So I do not understand this as a reason, but hey to each his own.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Only using clientside SQL open you up to the possibility of SQL injection attacks!!!
-
hmmm... maybe the semi-colon is not general across databases. Perhaps detect if SQL Server before using it otherwise you may have to use it. Also... is @@SCOPE_IDENTITY database general? If not, you may have to have 'dialects' for different databases... e.g. http://coldfusion.sys-con.com/read/43794.htm
I believe @@SCOPE_IDENTITY is not database general. Does your boss know this? If you really want to be database independant, you'll have to use 'dialects' as mfhobbs suggested or use an OR mapping tool such as nhibernate.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Wow! Your question has started a huge debate on so many related topics. I haven't read the entire thread, so I'm sure some of what I say will just be a rehash, but here's my 2 cents: 1) To be truely DB agnostic you need a Data Access Layer written for each Db. Your sample is proof of that (@@IDENTITY and SCOPE_IDENTITY()). 2) Stored procedures DO reduce network traffic, but the network is where the load is seen, not the database. Given the exact same resultset, a batch of 10 lines multiplied over 100s of queries per second WILL create a greater network load than using stored procedures. However, as long as your dynamic queries use parameters you will not see any significant load on the db. This however brings us back to #1 - Sql Server Parameters use @paramName while other vendors just use '?' (I have to admit I haven't used other vendors, this is just based on sample code I've seen). Anyhow, using stored procedures vs. dynamic sql is simply a design choice. Which must be weighed against your program's requirements. But your 'dynamic' sql should ALWAYS use parameters. The difference between using parameters and not using them is a factor of as much as 200 in favor of parameters. 3) using @@IDENTITY in the same batch runs the risk of pulling the wrong identity value and increases dramatically when run in a separate batch. 4) Do your web apps have any use to third parties? That's the first question to be asked. Unless your company is intending to change their business model and start adding technical support and investing time and money into deploying and supporting web applications on external systems and pulling resources away from your core functions, I don't see the point in even addressing the issue. I would say in most cases, applications written for internal use and then retro-fitted for use by third parties are begging for problems. I'm not just referring to portability issues here, I'm talking about culture. Applications are written with specific groups of end users in mind, in this case your internal company culture. Which means the UI is designed with your company in mind and the way they use the application. If a different company starts using your application they have different goals in mind and will want to use the application in different ways than you do based on their business processes and how they want to integrate with their existing IT systems.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Don't get your hopes up that inline SQL will just run on any database you want. Even coding directly to the ANSI specification (92, 99, or 03) does not guarantee you can just point your app to any database and have it work or work without any issues. (like performance issues) Even in your simple example, "select @@identity" will work on MSSQL databases. Just like Java: "Write once, test everywhere...." You might want to think about abstracting your data access into different interfaces. Something like having an MSSQL interface, a Access DB interface, a MySQL interface. This, combined with a good unit testing plan, can get you close to DB independence...
Cheers