DROP PROCEDURE `spGetUserLog1`//
CREATE DEFINER=`xxx`@`xxxxx` PROCEDURE `spGetUserLog1`(
IN sUsername VARCHAR(9), IN dtFrom DATETIME, IN dtTo DATETIME,
IN OFFSET SMALLINT(1), IN ROWCOUNT SMALLINT(1), IN iGMT VARCHAR(50))
BEGIN
DECLARE UId, SR, R, MR, D SMALLINT(1);
DECLARE G,iGMT VARCHAR(50);
SELECT UserId, SubReseller, Reseller, MainReseller, Distributor,GMT
INTO UId, SR, R, MR, D ,G
FROM Customers WHERE Username=sUsername LIMIT 1;
CASE
WHEN D !=0 THEN
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
CONVERT_TZ(SentAt,iGMT,G)FROM SmscLog a WHERE Username IN( SELECT Username FROM Customers WHERE DistributorId = ? )
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";
SET @DistributorId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);
PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @DistributorId, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;
WHEN MR !=0 THEN
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
CONVERT_TZ(SentAt,iGMT,G) FROM SmscLog a WHERE Username IN ( SELECT Username FROM Customers WHERE MainResellerId = ? )
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";
SET @MainResellerId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ',', ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);
PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @MainResellerId, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;
WHEN R !=0 THEN
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
CONVERT_TZ(SentAt,iGMT,G) FROM SmscLog a WHERE Username IN ( SELECT Username FROM Customers WHERE ResellerId = ? )
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";
SET @ResellerId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ',', ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);
PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @ResellerId, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;
WHEN SR !=0 THEN
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
CONVERT_TZ(SentAt,iGMT,G) FROM SmscLog a WHERE Username IN ( SELECT Username FROM Customers WHERE SubResellerId = ? )
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";
SET @SubResellerId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ',', ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);
PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @SubResellerId, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;
ELSE
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) AS Status,
CONVERT_TZ(SentAt,iGMT,G) FROM SmscLog a WHERE Username = ?
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";
SET @Username = sUsername;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ',', ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);
PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @Username, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;
END CASE;
END
在这种情况下，我要传递的IGMT是使用我们的日期函数在php页面中计算的，我希望在存储过程中使用此值。
但是，当我在php页面上执行它时，出现错误：
存储过程中未定义的变量IGMT。
有什么办法可以在此存储过程中传递此值？提前致谢..
最佳答案
您正在过程中构建一个sql语句，然后准备并执行它。问题是您在准备好的查询中使用文本“ iGMT”，而不是变量包含的值。
您需要将其换成一个占位符（？），然后将iGMT参数传递到execute语句中。因此，例如第一个查询将变为
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
CONVERT_TZ(SentAt,?,G) FROM SmscLog a WHERE Username IN ( SELECT Username FROM Customers WHERE ResellerId = ? )
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";
SET @DistributorId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);
PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @iGMT, @DistributorId, @SentFrom, @SentTo;
您需要对过程中的每个语句进行此更改