combine stored procedure in sql and my sql

Question

am writing stored procedure in mysql 5.1 and call from c#.net ..and i want use that procedure in sql server..if its possible..please explain..

Answer 1

You can use a stored procedure in both MySQL and SqlServer (as long as the syntax is recognized by both systems; it's possible you'll need to tweak it a bit, though). To use it in both systems, you'll need to "add" it first. On the SqlServer side, use the CREATE PROC command to add it. On the MySQL side, use CREATE PROCEDURE.

Answer 2

Stored Procedures use proprietary SQL dialect (Transact-SQL for SQLServer and MySQL dialect for MySQL) and aren't thus really portable. You'll have to port your stored procedure from one language to the other.

Answer 3

TSQL has a much richer syntax than the MySQL dialect, as well as the fact that MS SQL Server has a richer SQL syntax than MySQL, so, anything you write in MySQL will want to be modified to take advantage of what SQL Server offers.

Your stored procedure should be written to be optimized for the database of interest, so, even if you could copy it over directly, it would be a bad idea.

Depending on what your stored procedure does, you may find it better to use an ORM, such as LINQ or Hibernate or any of the many others, as they can abstract out the specific database.

Answer 4

As others have pointed out, it IS possibile to write portable stored procedures between MySQL and SQL Server, but to do so, you'll need to be sure that the proc in question uses ANSI SQL standards (and even then, you'll need to test since neither database implements a pure form of ANSI SQL).

Whether or not you want to do that is another question because of performance reasons; both database engines have their own dialects of SQL that are optimized to run well on those engines. If the application requires extremely high performance from the underlying database, you may have to sacrifice portabilty for performance.

Of course, there are exceptions to every rule, and without seeing the code, it's difficult to say if this specific case is portable or not.