No more stored procedures

[Siderite Zaqwedex]

That's the reason! Use a piece of code that runs a stored procedure and worry about the syntax in the database. The only reason I've found not to use stored procedures (in real life) is that the debugging is too hard. There is no reason to use ten layers and 100 stored automatically generated stored procedures when all you want is to write a single value in a simple table or something.

---------- Siderite

[Trooks]

From the managers point of view this may seem perfectly logical. However, assuming that you do not put business logic in your stored procedures it is rather trivial to adjust the stored procs for each RDBMS and in fact it is quicker than changing code in multiple locations. The real question here is not so much speed (although important) but maintenance costs and security. If a table changes then it should be easy to identify all stored procedures that access the table to update. This is far quicker than sorting though the various lines of code, classes and other mechanisms to find the offending sql string. From a security point of view (ala SQL Injection attacks) having stored procedures is better protection than dynamic SQL which the coder forgot to eliminate a rem character or character. Finally, I have worked with all major RDBMS backends and there are some differences in the SQL. The SQL however is different regardless of whether you call it in a dynamic query or stored proc. In fact it is easier to deploy to multiple RDBMS backends when you have scripts for each stored proc that are optimized for each backend language. Again, maintenance is cheaper. Let's face it PL/SQL, MySQL, or DB2 will cast a string to a date differently than T-SQL. These are easier to find when you are compiling the database scripts (yes they all throw errors on compile if they are wrong). Management should respond to the issues of maintaining separate code bases for separate back end databases. This really only works if you separate out the business logic into a separate area (usually class library) and keep the stored procs for CRUD work. My thoughts only and I will be interested to hear someone else speak.:rolleyes:

[Mcsquare]

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.

[mfhobbs]

And just use one sql call: recordset.open( "insert data into table; select @@SCOPE_IDENTITY" )

[GroundSloth]

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...

[mfhobbs]

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

[Nick Parker]

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

[mfhobbs]

If your guide can talk seriously about coupling and manage to conclude that exposing your relational object model to your client applications is a positive (without distracting to strawman performance discussions), I will buy it.

[Trooks]

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.

[SHentzel]

Wouldn't you want your db specific code IN the db instead of in your code? -- then your code doesn't have to change, you just create the corresponding stored procedure in the new database. Seems like this is exactly the wrong suggestion?

[slastnoy]

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.

[Grimolfr]

David Stone wrote:

That's the dumbest thing I've ever heard.

Not quite the dumbest, but it ranks pretty highly.

---

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM users WHERE clue IS NOT NULL GO

(0 row(s) affected)

[Blexrude]

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

[Grimolfr]

@@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

Need a Second Life?[^]

SELECT * FROM users WHERE clue IS NOT NULL GO

(0 row(s) affected)

[Drewcrewof2]

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)

[mfhobbs]

If using SQL Server, you can encrypt the stored procedures so they cannot be edited.

[Joel Palmer 0]

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

[djtcp]

Egad! Aside from the performance and stability issues, this throws the door wide open for Sql Injection attacks! Will your boss be training the developers in the various ways of preventing those? --- dj

[mfhobbs]

I agree, every project I worked on that used stored procedures had them in source control. (So far only by managing them as individual (*.sql) files rather than taking advantage of any integration features.)

[SWFLADeveloper]

Only using clientside SQL open you up to the possibility of SQL injection attacks!!!