How SQL Sever stored procedures are converted to MySQL

How SQL Sever stored procedures are converted to MySQL

Overall, SQL Sever and MySQL stored procedures are the same idea, but there is a great difference in syntax and structure.
1. Write the stored procedure in MySQL all the dbo is removed.
2. In SQL Sever Query Analyzer directly with the CREATE procedure, but in MySQL must be used delimiter
$$ to start, because in MySQL ";" For the end of the symbol, so we have to change the end of the symbol, $$ means that in the subsequent running process if you encounter $$, then
It can be concluded that the program is over, add $$ after the end of the end!
DELIMITER
$$
BEGIN
Create PROCEDURE PROC_
End $$
DELIMITER;
3.
Each SQL statement needs to be followed by an error;
4. Besides parameters, we usually define parameters in MSSQL.
CREATE PROCEDURE Proc_st
(@operator varchar (300),
@ProcDate datetime,
@ErrorLog varchar (8000) OUTPUT)
But in MySQL this format is not possible;
The first input parameter in MySQL is in to indicate that the output parameter is out, if not write, default is in, and second in MySQL there is no @ symbol, so all the @ symbol to go
Off
The above stored procedure is rewritten as MySQL.

Create PROCEDURE Proc_st

(

In operator varchar (300),

Inch
Procdate datetime,

Out
Errorlog varchar (8000)

)
5. Questions About Time

5.1 Get time format
In the MSSQL we come to get time is generally expressed in convert, for example
[email protected] = CONVERT (varchar (6), @ProcDate, 112);
The meaning of this sentence is to obtain the time of the year and the format is YYYYMM, 112 represents a format;
But in MySQL I modified the time has been error, so I changed a way of writing
Year (now ()) *100+month (now ()) so it is possible;
5.2
Convert time format
Similarly, using CONVERT (varchar (6), parameters, and "the") in MSSQL = @YearMonth can be used, but in MySQL I use cast casts to
CAST (
REP. Fact_date
As
CHAR (6))
6.
About the use of if
In the MSSQL
If ()
Begin
Program Fragments
End
Else
Begin
Program Fragments
End is available;
However, it is not recognized in MySQL, the If must be followed by then, and must be followed by each of the else if; As an end symbol, otherwise, no matter how you debug it.
The following is a function I rewrite, relatively simple, mainly to compare the syntax
CREATE Functionloan_period
(
Begindate int,

EndDate int
)
Returnsvarchar (8000)
BEGIN

if (enddate-begindate <= ' 3 ')
Then

Return ' 10 ';
End If;

if (enddate-begindate> ' 3 ' andenddate-begindate <= ' 6 ')
Then

Return ' 20 ';

End If;

if (enddate-begindate> ' 6 ' andenddate-begindate <= ' 12 ')

Then

Return ' 30 ';

End If;

Return ' 40 ';
END
7. About the increase or decrease in time
We usually get the date in MSSQL for the last one months.
CONVERT (VARCHAR (6), DATEADD ("Month",-1, @ProcDate), 112)
DateAdd is the built-in function of MSSQL;
But in MySQL there is no this function, then we should do, don't worry, in MySQL has date_sub function, basically can meet our needs, above this sentence change
After writing is complete
CAST (Date_sub (Procdate,interval1 MONTH) as CHAR (6))
8. About Table variables
In MySQL, the concept of table variables does not exist, but can be replaced with temporary tables, in MySQL we start a temporary table in general
Createtemporary
Table Temptotal to create, it is also a good thing, is that the temporary variable after the execution of the stored procedure will be automatically released, will not consume a lot of memory;
9. The following statement is often used in MSSQL
[Email protected]
VARCHAR (MAX);
But in MySQL does not have max this concept, I generally will use Varcahr (8000) to replace, the online controversy, said the maximum amount of varchar have, I have not tried, rewritten
Time to use it with caution.
10. Financial products in MSSQL will often use the following statement
[Email protected]
VARCHAR (MAX);
But in MySQL does not have max this concept, I generally will use Varcahr (8000) to replace, the online controversy, said the maximum amount of varchar have, I have not tried, rewritten
Time to use it with caution.
11. There is no isnull function in MySQL, so we use ifnull instead, use the same;

Select top ten bid from books	Select bid from books limit 0,10
Select Bsid[email protected] @IDENTITY	Select Bsid=last_insert_id ()
If xxx Begin -------- End Else if xxx Begin End	If xxx Then -------- Else If XXX Then ----- End If; End If;
exec SP a,b,c	Call SP (A,B,C)
GETDATE ()	Now ()
Day (XX)	DayOfMonth (XX)
Create procedure xxx @a int @b varchar (+) As	Create Procedure xxx (a int,b varchar ()) Begin End
Dynamic SQL statement exec (' xxxx ')
Can be declared at any location	Internal SP must be declared at the outset
Don't need	In addition to the control statement, a semicolon must be appended to the sentence
Cast (A as XXX)	If you are converting to a string, it is best to use CONCAT()
With (NOLOCK)	did not find the corresponding,
Create proc	No proc abbreviation, must be changed to create procedure
Cross-database access [XX]. [dbo]. [AAA]	Omit DBO:XX.AAA
Update bookvotes set a.votes=a.votes+ (select SUM (starvotes) from BookReviews where bid=a.bid) from bookvotes A,bookre Views B where A.status=0 and A.bid=b.bid and B.status=1;	Update bookvotes a,bookreviews b set a.votes=a.votes+ (select SUM (starvotes) from BookReviews where Bid=a.bid) where A.status=0 and A.bid=b.bid and B.status=1;

How SQL Sever stored procedures are converted to MySQL