微软sql到mysql的转换 [英] conversion microsoft sql to mysql

问题描述

我正在将它从 mssql 转换为 mysql，但它给了我这个错误:

I'm converting this into mysql from mssql but it is giving me this error:

错误代码:1415 不允许从函数返回结果集

我做错了什么?

DELIMITER $$  

CREATE    FUNCTION GetPositionList (orderDetID NUMERIC)  
RETURNS NVARCHAR(200)    
DETERMINISTIC   
BEGIN   
DECLARE strToReturn NVARCHAR(50);  
DECLARE strPos NVARCHAR(50);  
DECLARE strPosOtherRes NVARCHAR(50);  
DECLARE strTPos NVARCHAR(50);  
DECLARE strTPosOtherResr NVARCHAR(50)


SET strToReturn = '';  
SET strPos = SELECT `POSITION` FROM orderdetails WHERE OrderID = orderDetID;
SET strPosOtherRes = SELECT SecPosition FROM orderdetails WHERE OrderID = orderDetID;
SET strTPos = SELECT TeamPosition FROM orderdetails WHERE OrderID = orderDetID;
SET strTPosOtherResr = SELECT TeamSecPosition FROM orderdetails WHERE OrderID = orderDetID;


IF(strPos != '') THEN SET strToReturn = CONCAT(strPos , ',');
END IF;
IF(strPosOtherRes != '' ) THEN SET strToReturn = CONCAT(strToReturn ,  strPosOtherRes , ',');
END IF;
IF(strTPos != '' ) THEN SET strToReturn =  CONCAT(strToReturn ,  strTPos , ',');
END IF;
IF(strTPosOtherResr != '' ) THEN SET strToReturn =  CONCAT(strToReturn , strTPosOtherResr , ',');
END IF;     
RETURN SUBSTRING( strToReturn, 0, LENGTH(strToReturn) );  
END $$  
DELIMITER ; 

推荐答案

MySQL 中将值选入变量的语法为 select ... into.例如你可以写:

The syntax for selecting values into variables in MySQL is select ... into. For example you could write:

SELECT POSITION, SecPosition FROM orderdetails 
WHERE OrderID = orderDetID INTO strPos, strPosOtherRes;

消息不允许从函数返回结果集"意味着现在的 select 语句将从函数返回结果集，而函数只能返回单个值.

The message "Not allowed to return a result set from a function" means that the select statements as they stand now would be returning a resultset from the function, and functions can only return single values.

这篇关于微软sql到mysql的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！