Stored Procedures

A stored procedure is a procedure (like a subprogram in a regular computing language) that is stored (in the database). Correctly speaking, MySQL supports "routines" and there are two kinds of routines: stored procedures which you call, or functions whose return values you use in other SQL statements the same way that you use pre-installed MySQL functions like pi().

A stored procedure has a name, a parameter list, and an SQL statement, which can contain many more SQL statements. There is new syntax for local variables, error handling, loop control, and IF conditions. Here is an example of a statement that creates a stored procedure.

CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
INSERT INTO table1 VALUES (variable1);/* statement */
END /* end of block */

Stored procedures are something new for MySQL, so naturally you'll approach them with some caution. Stored procedures are fast! What we can say is that the MySQL server takes some advantage of caching, just as prepared statements do. There is no compilation, so an SQL stored procedure won't work as quickly as a procedure written with an external language such as C. The main speed gain comes from reduction of network traffic. If you have a repetitive task that requires checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that's stored on the server.

Stored procedures are components! Suppose that you change your host language -- no
problem, the logic is in the database not the application.

Stored procedures are portable! When you write your stored procedure in SQL, you know that it will run on every platform that MySQL runs on, without obliging you to install an additional runtime-environment package, or set permissions for program execution in the operating system, or deploy different packages if you have different computer types. That's the advantage of writing in SQL rather than in an external language like Java or C or PHP.

Stored procedures are stored! If you write a procedure with the right naming
conventions, for example saying chequing_withdrawal for a bank transaction, then people who want to know about chequing can find your procedure. It's always available as 'source code' in the database itself. And it makes sense to link the data with the processes that operate on the data, as you might have heard in your programming-theory classes.

Stored procedures are migratory! MySQL adheres fairly closely to the SQL:2003
standard. Others (DB2, Mimer) also adhere. Others (Oracle, SQL Server) don't adhere
but I'll be providing tips and tools that make it easier to take code written for another DBMS and plunking it into MySQL.

CREATE PROCEDURE p1 () SELECT * FROM t;

CREATE PROCEDURE p2 ()
SELECT CURRENT_DATE, RAND() FROM t

Call Procedure

Now, to call a procedure, all you have to do is enter the word CALL and then the name of the procedure and then the parentheses. Again, the parentheses are compulsory.

CALL p1()

Cartesian product

Simply, it is a join without a Where clause. It gives you every row in the
first table, joined with every row in the second table.

SELECT * FROM AntiqueOwners, Orders;

schema

A schema is a logical set of tables, such as the Antiques database above...usually,
it is thought of as simply "the database", but a database can hold more than one schema. For example, a star schema is a set of tables where one large, central table holds all of the important information, and is linked, via foreign keys, to dimension tables which hold detail information, and can be used in a join to
create detailed reports.

General tips you would give to make my SQL queries and databases better and faster

You should try, if you can, to avoid expressions in Selects, such as SELECT ColumnA +
ColumnB, etc. The query optimizer of the database, the portion of the DBMS that determines the best way to get the required data out of the database itself, handles expressions in such a way that would normally require more time to retrieve the data than if columns were normally selected, and the expression itself handled programmatically.
Minimize the number of columns included in a Group By clause.
If you are using a join, try to have the columns joined on (from both tables) indexed.
When in doubt, index.
Unless doing multiple counts or a complex query, use COUNT(*) (the number of rows
generated by the query) rather than COUNT(Column_Name)

SQl Limitation

The limitation for the total number of objects in a database is 2,147,483,647. This includes all the objects (Tables, views, functions, stored procedures etc).

COALESCE

Returns the first nonnull expression among its arguments.

COALESCE ( expression [ ,...n ] )

If all arguments are NULL, COALESCE returns NULL.

ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements.
COALESCE(), on the other hand, can take multiple inputs.

SQL Server views a column created by COALESCE() as nullable, whereas one using ISNULL() is not.

Many uses of Coalesce
http://www.mssqltips.com/tip.asp?tip=1521

Convert vbscript code to Javascript

http://slingfive.com/pages/code/scriptConverter/demo.html

http://slingfive.com/pages/code/scriptConverter/

How to get a list of all user defined functions (along with source code) existing in an SQL Server 2000 database using JavaScript

http://www.devarticles.com/c/a/JavaScript/Digging-More-into-SQL-Server-2000-Using-Clientside-JavaScript/

http://www.devarticles.com/c/a/JavaScript/Digging-More-into-SQL-Server-2000-Using-Clientside-JavaScript/1