CREATE DEFINER=`root`@`%` PROCEDURE `SIMPLEPROC`(
IN USER_ID INT,
IN STEP INT,
INOUT CPIC_ID INT,
OUT FILENAME VARCHAR(100)
)
BEGIN
SET @TYPER = CONCAT('TYPER', STEP);
SET @STATE = CONCAT('STATE', STEP);SET @SQL=CONCAT('UPDATE ', @T, ' SET ',
@TYPER, '=', USER_ID, ',', @STATE, '=1 WHERE PIC_ID>=', CPIC_ID,
' AND ', @STATE, '=0 LIMIT 1;');
PREPARE STMT1 FROM @SQL;
EXECUTE STMT1;
DEALLOCATE PREPARE STMT1;
SET @SQL = CONCAT('SELECT CPIC_ID = `PIC_ID`, FILENAME = `PICNAME` FROM `STATE` WHERE ',
@TYPER, '=', USER_ID, ' AND ', @STATE, '=1 LIMIT 1'); SELECT @SQL;
PREPARE STMT2 FROM @SQL;
EXECUTE STMT2;
DEALLOCATE PREPARE STMT2;
END;
IN USER_ID INT,
IN STEP INT,
INOUT CPIC_ID INT,
OUT FILENAME VARCHAR(100)
)
BEGIN
SET @TYPER = CONCAT('TYPER', STEP);
SET @STATE = CONCAT('STATE', STEP);SET @SQL=CONCAT('UPDATE ', @T, ' SET ',
@TYPER, '=', USER_ID, ',', @STATE, '=1 WHERE PIC_ID>=', CPIC_ID,
' AND ', @STATE, '=0 LIMIT 1;');
PREPARE STMT1 FROM @SQL;
EXECUTE STMT1;
DEALLOCATE PREPARE STMT1;
SET @SQL = CONCAT('SELECT CPIC_ID = `PIC_ID`, FILENAME = `PICNAME` FROM `STATE` WHERE ',
@TYPER, '=', USER_ID, ' AND ', @STATE, '=1 LIMIT 1'); SELECT @SQL;
PREPARE STMT2 FROM @SQL;
EXECUTE STMT2;
DEALLOCATE PREPARE STMT2;
END;
@TYPER, '=', USER_ID, ' AND ', @STATE, '=1 LIMIT 1');SELECT @SQL;
PREPARE STMT2 FROM @SQL;
EXECUTE STMT2;
DEALLOCATE PREPARE STMT2;调用:
SET @C=1;
SET @F="BJMHNJKKLJL";
CALL `SIMPLEPROC` (1234,2, @C, @W)
这一段 问题在哪里?
@TYPER, '=', USER_ID, ' AND ', @STATE, '=1 LIMIT 1');SELECT @SQL;另 这一段的结果 为什么是 空串 呢?
@TYPER, '=', USER_ID, ',', @STATE, '=1 WHERE PIC_ID>=', CPIC_ID,
' AND ', @STATE, '=0 LIMIT 1;');
检查一下这个语句的结果是什么select @sql
SELECT CPIC_ID=`PIC_ID`, FILENAME=`PICNAME` FROM `STATE` WHERE TYPER2=1234 AND STATE2=1 LIMIT 1 现在怎样将 `PIC_ID`,`PICNAME` 字段值取出到输出参数呢?多谢热心相助!