SQL Sever stored procedures are converted to MySQL's

Generally speaking,SQL Severand theMysqlthe idea of stored procedures is the same, but there is a big difference in syntax and structure.
1. Write the stored procedure in MySQL all the dbo is removed.
2 . 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, @ProcDate datetime, @ErrorLog varchar (8000) OUTPUT)

but inMySQLin this format is not possible;
First inMySQLthe input parameter isinchto indicate that the output parameter is outindicates that, if not written, the default isinch, followed byMySQLis not in@symbols, so all the@The symbols are to be removed .
The stored procedure described above is rewritten asMySQLwords

Create PROCEDURE Proc_st (in operator varchar), in procdate datetime, out errorlog varchar (8000))

5. questions About Time
5.1 Get time format
in the MSSQL we're going to get the time in general. CONVERT to represent, for example,

[email protected] = CONVERT (varchar (6), @ProcDate, 112);

The meaning of this sentence is to get the time of year and the format is yyyymm of, the represented by a format;

but in MySQL I changed the time but always error, so I changed a way of writing

Year (now ()) *100+month (now ())

so that it can be done;
5.2
Convert time Format
Similarly, inMSSQLin useCONVERT (varchar (6),Parameters) = @YearMonthcan be used, but inMYSQLI used it.castThe cast is only available

CAST (REP. Fact_date as CHAR (6))

6.

about theifthe Use
in theMSSQLinwith
If ()
Begin
Program Fragments
End
Else
Begin
Program Fragments
Endit can be;
but inMysqlis not recognized in theifmust be followed by a Then, and each oneelse ifmust be followed by a;as an end symbol, or whatever you do, it doesn't matter.
The following is a function I rewrite, relatively simple, mainly to compare the syntax

CREATE functionloan_period (begindate int,enddate int) Returnsvarchar (8000) beginif (enddate-begindate <= ' 3 ') then return '; end if;if (enddate-begindate> ' 3 ' andenddate-begindate <= ' 6 ') then return '; End If;if ( Enddate-begindate> ' 6 ' andenddate-begindate <= ') then return ' to '; end If;return ' 40 '; END

7. about the increase or decrease in time
we are generally MSSQL The last one months of the desired date are usually written like this .

CONVERT (VARCHAR (6), DATEADD ("Month",-1, @ProcDate), 112)

Span style= "FONT-FAMILY:CALIBRI;" > DATEADD is MSSQL built-in functions;

but in MySQL There is no such function, so what should we do, do not worry, in MySQL in a date_sub function, basically can meet our needs, above this sentence rewrite completed

CAST (Date_sub (Procdate,interval1 MONTH) as CHAR (6))

8. about table variables

in theMYsqlThe concept of a table variable is not present, but it can be replaced by a temporary table,Mysqlwe are generally used in the temporary form of business
Createtemporary
Table Temptotalto create, there is also the advantage that temporary variables will be released automatically after the execution of the stored procedure, and will not consume a lot of memory;
9.in theMSSQLThe following statement is often used in the
declare@temp
VARCHAR (MAX);
but inMySQLnot inMaxThis concept, I would normally useVarcahr(8000) to replace the online controversy, sayingvarcharThe maximum value of how much is there, I have not tried, rewrite the time to use with caution.
10.in theMSSQLChinese financial products will often use the following statement
Declare@temp
VARCHAR (MAX);
but inMySQLnot inMaxThis concept, I would normally useVarcahr(8000) to replace the online controversy, sayingvarcharThe maximum value of how much is there, I have not tried, rewrite the time to use with caution.
There is no isnull function in MySQL , so we use ifnull instead, Use the same;
The above is the process of my rewriting some of the problems encountered, I hope you can help it!

SQL Sever stored procedures are converted to MySQL