MYSQL与SQLserver之间存储过程的转换方式

CREATE DEFINER=`root`@`%` PROCEDURE `searchProduct`(
 
 IN cone VARCHAR ( 30 ),
 IN ctow VARCHAR ( 30 ),
 
 IN page INT,
 IN size INT
 )
BEGIN
   set @s='SELECT *  FROM  productclass where status=0';
 
--   if(pname is not null) and pname!=''
--   then set @s=concat(@s,' and product LIKE \'','%',pname,'%','\'');
--   end if;
  if(cone is not null) and cone!=''
  then set @s=concat(@s,' and class1 LIKE \'','%',cone,'%','\'');
  end if;
  if(ctow is not null) and ctow!=''
  then set @s=concat(@s,' and class2 LIKE \'','%',ctow,'%','\'');
  end if;
  
  
  set @s=concat(@s,' ORDER BY class1,class2,class3,class4');
  if(size>0) then
  set @s=concat(@s,' limit ',(page-1)*size,',',size);
  end if;
  -- 拼接完成后可以调用 select @s 语句，查看最终拼接的sql语句是否正确
  prepare stmt from @s;-- 预编译一条sql语句，并命名为stmt
  execute stmt;-- 执行预编译sql
END

ALTER PROCEDURE [dbo].[searchProduct]
@cone VARCHAR ( 30 ),@ctow VARCHAR ( 30 ),@page INT,@size INT
AS
BEGIN
 -- routine body Goes here, e.g.
 -- SELECT 'Navicat for SQL Server'
 declare @s Nvarchar(MAX);
 set @s='SELECT *  FROM  productclass where status=0';
 
--   if(pname is not null) and pname!=''
--   then set @s=concat(@s,' and product LIKE \'','%',pname,'%','\'');
--   end if;
  if(@cone is not null) and @cone!=''
  BEGIN
   set @s=concat(@s,' and class1 LIKE ','''%',@cone,'%''');
  END
  if(@ctow is not null) and @ctow!=''
  BEGIN
   set @s=concat(@s,' and class2 LIKE ','''%',@ctow,'%''');
  END
  
  
  set @s=concat(@s,' ORDER BY class1,class2,class3,class4');
  if(@size>0)
  BEGIN
   set @s=concat(@s,'( select top ',@size,' id from productclass
       where id not in ( 
       select top ', (@page-1)*@size,' id from productclass 
     ))')
  END
  -- 拼接完成后可以调用 select @s 语句，查看最终拼接的sql语句是否正确
  print(@s)
  EXEC sp_executesql @s;
END