Some common usage differences between MySQL and SQL Server

This article will mainly list MySQL and SQL Server different places, and the common stored procedures related to the main content.

1. Identifier Qualifier

Sql server	[]
Mysql	``

2. Adding strings

Sql server	Directly with +
Mysql	Concat ()

3. IsNull ()

Sql server	IsNull ()
Mysql	Ifnull () Note: MySQL also has the isnull () function, but the meaning is different

4. GETDATE ()

Sql server	GETDATE ()
Mysql	Now ()

5. NEWID ()

Sql server	NEWID ()
Mysql	UUID ()

6. @ @ROWCOUNT

Sql server	@ @ROWCOUNT
Mysql	Row_count () Note: This function of MySQL is valid only for UPDATE, INSERT, delete

7. Scope_identity ()

Sql server	Scope_identity ()
Mysql	LAST_INSERT_ID ()

8. If ... else ...

Sql server	IF boolean_expression {sql_statement | statement_block} [ELSE {sql_statement | statement_block}] --To define Statement block, use the control flow keyword BEGIN and END.
Mysql	IF search_condition then statement_list [ELSEIF search_condition then statement_list] ... [ELSE Statement_list] END IF

Note: For MySQL, then, end if is required. Similar to other flow control statements, this is not listed here.

9. Declare

In fact, SQL Server and MySQL have this statement to define variables, but the difference is: in MySQL, declare is only used in the begin ... End Compound statement, and must precede any other statement at the beginning of the compound statement. This requirement will feel very BT when writing the cursor.

10. Notation of cursors

SQL Server

declare @tempShoppingCart table (ProductId int, Quantity int) insert INTO @tempShoppingC Art (ProductId, Quantity) Select ProductId, Quantity from ShoppingCart where userguid = @UserGuiddeclare @productId intdecl is @quantity intdeclare tempcartcursor cursor for select ProductId, quantity from @tempShoppingCartopen Tempcartcursorfe Tch next from Tempcartcursor to @productId, @quantitywhile @ @FETCH_STATUS = 0beginupdate Product Set sellcount = Sellco UNT + @quantitywhere productId = @productIdfetch Next from tempcartcursor to @productId, @quantityendclose Tempcartcurso Rdeallocate tempcartcursor

Mysql

DECLARE m_done int default 0;declare m_sectionid int;declare m_newsid int;declare _cursor_sn cursor for select SectionID, NewSID from _temp_sn;declare continue handler to not found set M_done = 1;create temporary table _TEMP_SN Select Sectioni D, NewSID from Sectionnews Group by SectionID, NewSID have count (*) > 1;open _cursor_sn;while (m_done = 0) Dofet Ch _CURSOR_SN to M_sectionid, m_newsid;if (m_done = 0) then--specific processing logic end if;end while;close _cursor_sn;drop table _tem P_SN;

Note: To improve performance, you typically open a cursor on a table variable and do not open the cursor directly on the data table.

11. Pagination Processing

SQL Server

CREATE PROCEDURE Getproductbycategoryid (@CategoryID int, @PageIndex int = 0, @PageSize int = A, @TotalRecords int output) Asbegin declare @ResultTable table (RowIndex int, Product ID int, ProductName nvarchar (+), CategoryID int, Unit nvarchar (ten), UnitPrice money, Quantity int); Insert INTO @ResultTable Select Row_number () over (order by ProductID ASC) as RowIndex, P.productid, p.product Name, P.categoryid, P.unit, P.unitprice, p.quantity from products as P where CategoryID = @CategoryID; Select @TotalRecords = count (*) from @ResultTable; SELECT * from @ResultTable where RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex + 1)) ; End; Of course, this is not the only way SQL Server is written, but it's more common.

Mysql

CREATE PROCEDURE Getproductsbycategoryid (in _categoryid int, in _pageindex int, in _pagesize int, out _totalreccount int) begin Set @categoryId = _categoryid; Set @startRow = _pageindex * _pagesize; Set @pageSize = _pagesize; Prepare Pagesql from "Select Sql_calc_found_rows * from Product where categoryId =? ORDER BY ProductId desc limit?, ? '; Execute pagesql using @categoryId, @startRow, @pageSize; deallocate prepare pagesql; Set _totalreccount = Found_rows (); End

There are too many differences between MySQL and SQL Server, just a list of some of the specific differences that I think are often encountered in writing stored procedures.

Transferred from: http://www.cnblogs.com/fish-li/archive/2011/04/05/2006107.html

Some common usage differences between MySQL and SQL Server