asked on 5/19/2011

Sample mySQL routine/procedure similar to MS SQL stored procedure

Unfortunately new to mySQL... been using MS SQL for # years. Want to get my feet wet with mySQL.
Have created a database in mySQL and want to create similar 'procedures' as I would use in MS SQL.

I'm including a simple stored procedure I would create in MS SQL passing parameters from VB.NET for a SELECT.

Could someone show me what it would look like in mySQL? I've been looking at 'passing parameters' examples. Lost as to whether a MS SQL stored procedure is saved in mySQL as a routine or what?

Quick learner. :-)
Thanks.

USE [A_DATABASE]
GO
/****** Object:  StoredProcedure [dbo].[SPS_SelectItems]    Script Date: 05/18/2011 21:21:35 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[SPS_SelectItems]
@CompanyID bigint
AS
SELECT a.*
FROM purch_item a
WHERE a.company_id = @CompanyID
ORDER BY a.item_description

Open in new window

MySQL Server Microsoft SQL Server

Last Comment

Kevin Cross

5/19/2011

ASKER CERTIFIED SOLUTION

Kevin Cross

5/19/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

TSFLLC

5/19/2011

ASKER

When you edit a stored procedure in MS SQL, depending on the defaults you have set, the USE [?] may or may not be included in the SP. I'm assuming it's optional in a mySQL procedure and preferable NOT to include?

If so, what are the ramifications or sticking points in including it or not including. In my MS SQL-based marketable apps allow for installation of SQL Server where the installer can change the name of the database in the installation and the connection strings used to connect in VB have that database as the initial database. It would be catastrophic if they were hard-coded I would think.

At any rate, this is exactly what I was looking for.

Thanks Kevin.

Kevin Cross

5/19/2011

You are most welcome. Yes that is the ramification. I tend to not have the database name in there unless I want something in a specific schema like with utility functions that I want created the same on all my systems. Otherwise, I leave the code unadourned and connect to the db I want before executing. It has bitten me in the tail once or twice to have code that I intended to execute on a development system that executed in production. Formally, none resulted in major damage. That is the major downside to hardcoding when running scripts as an admin. As you said, it can cause application flexibility issues also.

Good luck!

Best regards and happy coding,
Kevin