Fog Creek Software
Discussion Board




Are Microsoft SQL's stored procedures standard?

I am writing an app that I feel would be easier if I used stored procedures.

My ideal SQL Server for this is MySql.

However, it currently will not support stored procedures until version 5.

If I develop the app using microsoft Sql stored procedures will I be able to use the code in mysql when it eventually has them or has most of T-SQL been modified to be microsoft only?

Or,

Is there another sql server product I can use which has standard stored procedures?

Regards

Mike Grace
Friday, April 11, 2003

I have only used a half dozen or so different databases, but none them seem to have the same SQL stored procedure language. Some are similar, some are miles apart.

If you want to support multiple databases you should build some kind of persistence layer that allows you to change databases easily. But you will have to maintain slightly different DB schemas for each.

Craig
Friday, April 11, 2003


If you keep your stored procedures simple (pretty much standard IBM sql with parameters), you could just write a perl script to translate when you switch DB's.

good luck!

Matt H.
Friday, April 11, 2003

"My ideal SQL Server for this is MySql"  or
"My ideal SQL server for this is MySql"  ??

na
Friday, April 11, 2003

Probably: "My ideal: SQL Server, for this is MySQL." ;-)

Just me (Sir to you)
Friday, April 11, 2003

... or ... The DBMS I would like to use is mySQL.


Friday, April 11, 2003

If I understand it correctly, you want to use stored procedures in MySQL, but that can't be done yet. Therefore you want to use MS SQL Server in the meantime, in a way that you can transfer the stored procedures easily back to MySQL afterwards.

Have you thought about using PostgreSQL or one of the other OS-databases that support stored procedures _now_? It might just circumvent your problem.

Roel Schroeven
Friday, April 11, 2003

I believe there is no standard stored procedure langange.

Each RDBMS has a different implentation of stored procedures and they have different capabilities.

The SQL code for your Selects and Inserts should be the same but the procedural control langauge would have to be rewritten for each vendor.

DJ
Friday, April 11, 2003

Something a little off-topic, but with the next version (Yukon) of SQL Server having .NET CLR integrated, developers will be able to write stored procedures in 23 languages in addition to Microsoft's T-SQL. How cool (or cold) is that?!?!

BTW, does MySQL 3.x or 4.x support stored procedures? I thought stored procedures in MySQL were being implemented in version 5.0 development tree. And that effort is based on SQL-99, which has a basic syntax similar (but not identical) to Oracle PL/SQL.

ashben
Friday, April 11, 2003

"How cool (or cold) is that?!?!"

Not very.  Why create a system that moves away from a standard? 

Nick
Friday, April 11, 2003

Vendor lock in

And the horse you rode in on
Friday, April 11, 2003

Nick: "Why create a system that moves away from a standard?"

Probably, because MS has this thing about creating their own standards (for the rest to follow).

ashben
Friday, April 11, 2003

Ask Oracle about embedding Java. Everybody tries to maneuver to lock into their products, not just Microsoft.

Brad (dotnetguy.techieswithcats.com)
Friday, April 11, 2003

you can already use java in Oracle, and TCL and some other stuff within Postgres. both suck, however. The stored procedure language you will really use will be PL/SQL, pg/plsql, or T-SQL, depending on the database. All of these languages suck.

There are various religions surrounding the use of stored procedures. Some people argue that you should use them as much as possible: let the database do as much of the work as you can, you'll get performance gains this way, if you don't do it this way you aren't doing enough db design up front.

Other people argue that stored procedures suck, they are written in weird non-standard languages that are no fun to write in, and that are hard to debug. 

I've been in both situations and for the most part, i try to avoid stored procedures, because debugging them really fucking sucks. However, if you have a really crazy data model, you might need to use them, to keep sane. But what i've noticed is that you don't really need to use stored procedures until your database gets complicated enough to warrant having a separate "database team" of DBAs and database programmers ONLY doing database stuff.

choppy
Friday, April 11, 2003

Choppy:

Isn't the complexity of the DB evident from the get go?  It sounds like you are talking about a product that adds tables in a adhoc manner, thus the need to hack your way around using stored procs....oh and by the way, thats the exact scenario that exists where i work!

Don't get into the situation, in a development environment,  where you have a dedicated "database team".  This will cause much heart-ache and head-ache.  I would stand clear of such organizations that have such.

apw
Friday, April 11, 2003

"Isn't the complexity of the DB evident from the get go? "

Not necessarily. Obviously if you are setting out to write an inventory management system for chrysler, you realize you are going to be dealing with something complex. However, most DB projects I work on are much smaller scale (10-30 tables) and thus complexity doesn't start spiraling out of control until years later when people want to add more functions to the system.

" Don't get into the situation, in a development environment,  where you have a dedicated "database team".  This will cause much heart-ache and head-ache.  I would stand clear of such organizations that have such.                        "

Well, if you get heart-ache from programming, you need to learn how to separate your emotional life from your professional life. And much head-ache seems to accompany any sort of development task. One of my contracts is at a hospital, that has a huge database server farm, and the databases have thousands of tables, and you can't fuck up the data, or else you will not only be fired, you are liable for being sued due to HIPAA regulations. I'm certainly glad there is a dedicated "database team" there, because I already have two jobs, and don't need another four!

choppy
Friday, April 11, 2003

Nick: Why create a system that moves away from a standard?


Actually, MS is moving *toward* a standard here. The MS "stored procedure language" (T-SQL (Transact Structured Query Language)) is currently a proprietary, bastardized "embrace and extend" hack to the SQL-92 "standard" SQL.

By moving to CLR support by the query engine, they are moving from a proprietary hack *to* something that's already an ECMA standard, and what will soon be an ISO standard.

They're actually moving in the right direction with this and I'm really jazzed these days about development in C# -- and since I spend 90% of my time hacking out T-SQL I'm anxiously awaitning the inclusion of the CLR in the query processing engine. I think it's overall a good move for MS.

REF: http://msdn.microsoft.com/net/ecma/
for further details.

Sergent Sausage
Friday, April 11, 2003

Stored procedures have been part of the ISO SQL Standard since the 1990s. There is an excellent standard syntax specified in the PSM package (Persistent Stored Modules).

The ThinkSQL RDBMS complies with the standard: http://www.thinksql.co.uk

Greg Gaughan
Saturday, April 12, 2003

Maybe you would like to check FireBird RDBMS - open source form of Borlands Interbase.
Have 3 SQL dialects, available on various platforms.
Have a look at www.ibphoenix.com 

drazen
Sunday, April 13, 2003

I have to agree with Sergent  comments.

The real problem here is why learn another procedural language?

I have been saying for years that sql server should have also allowed VBA in addition to t-sql as a programming language. MS missed a huge opportunity here. So many companies started down the road of 3 tiered applications because t-sql sucked for complex procedures, and placing all the business code in the front end also did not make sense (developers need a half decent IDE, and t-sql does not give that). Anyone who has worked on databases with a nice programming language integrated into the database system will understand this. IBM’s UniVerse line, and pick were such database systems with a nice programming language.

Even the older dbase programming languages were considered much better then t-sql, but dbase died since the design was never migrated to a “server” based version of dbase. It would be a different world if dbase had really thought about this. At least Microsoft did think about client server, and created DAO, and then ADO data objects.

I seen a lot of companies with developers that have both sql skills, and good VB skills. They then bring in another developers with t-sql skills. This should NOT have to be the case.

If the new version of sql server supports all the net languages, then Microsoft has indeed scored another brilliant design decision. What a fabulous idea! Once again, they are going to kick butt here.

I will actually admit, that a good portion of my work actually involves using ms-office to provide solutions. The reason why office is so much better then something like open “star office” is that I can use my VB skills, and simply develop applications around ms-office.  VBA is a “super glue” that holds all of those appcltions together. I even go so far as to say that one of the most useful technologies that MS came out with was the inclusion of VBA into office.

I have written medical billing systems in ms-word that interface to propriety database systems. Knowing VB (well, ok VBA) was the ticket here.

Anyone who has hit alt-f11 while using ms-word will understand what I mean (every copy of word has the  VB6 ide built in. If you never hit alt-f11 while using word…you should!!).

I have never meet ONE business that cannot use some form of VBA to automate and integrate office into their existing work flow. Using all these rich applications together as programmable objects is a dream come true for me.

Having a completely programmable sql server, or word processor, or Excel all makes sense. It also all makes sense that every time a new system comes out, I don’t have to learn a new language. While much of what I am talking about is really the fact of COM objects, having a common language sure is nice.

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Sunday, April 13, 2003

Hmm.

Maybe its better to try and steer clear of stored procedures.

I wanted to use them because I have another guy to does a lot of reports and it would be easier for him.

Mike Grace
Monday, April 14, 2003

Re: Pros and cons of SP's.

I have come across this too, and its mostly avid C/Java programmers that seem to be making up the "never-use-stored-procedures" crowd.

I've got specs where it explicitly says:

"Stored procedures are not to be used."  - whats up with that? ... Maybe the spec writer had problems with some SP back in the 80's :)

Preparing zillions of reports tends to suck royally every single time. This is where stored procedures and views in databases shine. You get so much "boring" stuff for free in SQL stored procedures, like all the statistical functions and what have you.

Instead of doing all this averageing and standard deviation or other functions in C or whatever, simply do them in stored procedures and make a view out of it. Then doing reports is as simple as SELECT * FROM REPORT_FOO.

Saying "never" is bad. Most SP languages I've come across are pretty simple and easy to learn if you know programming in any other language.

Just my 2 cents,

Patrik
Monday, April 14, 2003

Do be careful, though.

There is a big difference between Oracle and MS-SQL stored procedures

MS-SQL SPs can return record sets. Oracle SP's don't return anything. Oracle has Stored Functions but these only return one value (as you might expect)

It would be interesting to know whether other RDBMS's work this way as well.

LC
Monday, April 14, 2003

can we create store procedure in MySql 4.X,if possible ,pls write me the syntax. if not ,pls tell me its solution.

pragnya dipta martha
Friday, April 16, 2004

"There is a big difference between Oracle and MS-SQL stored procedures ".  Yes I agree but syntax is only different and yes you can easily return record set (type of  ref cursor) from a stored procedure as anything else you can do with MS-SQL stored procedures. How it is easy to create/debug an Oracle stored procedure I illustrated in the next example:

Create the following Oracle package on the Oracle server:

CREATE OR REPLACE PACKAGE curspkg_join AS
      TYPE t_cursor IS REF CURSOR ;
      Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);
  END curspkg_join;
  /
show errors;
                
Create the following Oracle package body on the Oracle server:

  CREATE OR REPLACE PACKAGE BODY curspkg_join AS
  Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)
  IS
      v_cursor t_cursor;
  BEGIN
      IF n_EMPNO <> 0
      THEN
          OPEN v_cursor FOR
          SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
              FROM EMP, DEPT
              WHERE EMP.DEPTNO = DEPT.DEPTNO
              AND EMP.EMPNO = n_EMPNO;

      ELSE
          OPEN v_cursor FOR
          SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
              FROM EMP, DEPT
              WHERE EMP.DEPTNO = DEPT.DEPTNO;

      END IF;
      io_cursor := v_cursor;
  END open_join_cursor1;
  END curspkg_join;
  /
show errors;

stan vuk
Saturday, April 17, 2004

*  Recent Topics

*  Fog Creek Home