大家好，我寫了個stored procedure,運行時候一直報錯，Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1。
能不能幫忙看看，我寫的表和stored procedure 哪有問題，糾結了幾天了。。。，先謝謝大家啦！
DELIMITER $$
CREATE DEFINER=`catavolt`@`%` PROCEDURE `sp_create_timesheet`(IN p_TechId varchar(10), IN p_Date varchar(10))
BEGIN
declare w_last_inserted_record integer;
declare w_pIx integer ;
declare w_update varchar(21845);
declare w_where varchar(2048);
declare w_sql varchar(21845);
declare w_finished integer;
declare w_P_Num integer;
declare w_Service_WON nvarchar(10);
declare w_Comments nvarchar(200);
declare w_TotalHours decimal(4,2);
declare w_OT_Code nvarchar(10);
declare w_JobComplete nvarchar(5);
DECLARE cr CURSOR FOR SELECT `Service_WON`,
`Comments`,`TotalHours`,`OT_Code`,`JobComplete`
FROM `cv_vw_newtransactionsumadmin`
WHERE `PersonID` = p_TechId and `OrderDate` = p_Date;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET w_finished = 1;
OPEN cr;
SET SQL_SAFE_UPDATES = 0;
DELETE FROM `cv_timesheet` WHERE `TechName` = p_TechId and `OrderDate` = p_Date ;
INSERT INTO `cv_timesheet` (`TechName`, `OrderDate`)
SELECT `PersonID`,`OrderDate`
FROM `Catavolt`.`cv_vw_newtransactionsumadmin`
where `PersonID` = p_TechId and `OrderDate` = p_Date group by PersonID;
set w_where = concat(' WHERE TechName = ''', p_TechId, ' and OrderDate = ''', p_Date, '''');
set w_update = ' ';
set w_pIx = 1;
set w_finished = 0;
get_details: loop
FETCH cr INTO w_Service_WON, w_Comments, w_TotalHours, w_OT_Code,w_JobComplete; -- , w_Warehouse
if (w_finished = 1 or w_pIx > 10) then
leave get_details;
end if;
set w_update = concat(w_update, '`P_Num', w_pIx, '` = ', w_pIx, ', ');
set w_update = concat(w_update, '`WONo', w_pIx, '` = ''', w_Service_WON, ''', ');
set w_update = concat(w_update, '`Description', w_pIx, '` = ''', w_Comments, ''', ');
set w_update = concat(w_update, '`Hours', w_pIx, '` = ', w_TotalHours, ' , ');
set w_update = concat(w_update, '`WorkCodes', w_pIx, '` = ''', w_OT_Code, ''', ');
set w_update = concat(w_update, '`JC_JNC', w_pIx, '` = ''', w_JobComplete, ''', ');
set w_pIx = w_pIx + 1;
end loop get_details;
CLOSE cr;
set w_update = mid(w_update, 1, (length(w_update) - 2));
set w_sql = concat('update `cv_timesheet` set ' , w_update, ' ', w_where);
SET @s = w_sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;
CREATE TABLE `cv_timesheet` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`P_Num1` int(4) DEFAULT NULL,
`WONo1` int(11) DEFAULT NULL,
`WOLineNumber1` varchar(3) DEFAULT NULL,
`Customer1` varchar(50) DEFAULT NULL,
`Description1` varchar(1150) DEFAULT NULL,
`Hours1` decimal(4,2) DEFAULT NULL,
`WorkCodes1` varchar(10) DEFAULT NULL,
`JC_JNC1` varchar(5) DEFAULT NULL,
`WorkCodesNote1` varchar(300) DEFAULT NULL,
`ProjectCodesNote1` varchar(300) DEFAULT NULL,
`P_Num2` int(4) DEFAULT NULL,
`WONo2` int(11) DEFAULT NULL,
`WOLineNumber2` varchar(3) DEFAULT NULL,
`Customer2` varchar(50) DEFAULT NULL,
`Description2` varchar(1150) DEFAULT NULL,
`Hours2` decimal(4,2) DEFAULT NULL,
`WorkCodes2` varchar(10) DEFAULT NULL,
`JC_JNC2` varchar(5) DEFAULT NULL,
`WorkCodesNote2` varchar(300) DEFAULT NULL,
`ProjectCodesNote2` varchar(300) DEFAULT NULL,
`P_Num3` int(4) DEFAULT NULL,
`WONo3` int(11) DEFAULT NULL,
`WOLineNumber3` varchar(3) DEFAULT NULL,
`Customer3` varchar(50) DEFAULT NULL,
`Description3` varchar(1150) DEFAULT NULL,
`Hours3` decimal(4,2) DEFAULT NULL,
`WorkCodes3` varchar(10) DEFAULT NULL,
`JC_JNC3` varchar(5) DEFAULT NULL,
`WorkCodesNote3` varchar(300) DEFAULT NULL,
`ProjectCodesNote3` varchar(300) DEFAULT NULL,
`P_Num4` int(4) DEFAULT NULL,
`WONo4` int(11) DEFAULT NULL,
`WOLineNumber4` varchar(3) DEFAULT NULL,
`Customer4` varchar(50) DEFAULT NULL,
`Description4` varchar(1150) DEFAULT NULL,
`Hours4` decimal(4,2) DEFAULT NULL,
`WorkCodes4` varchar(10) DEFAULT NULL,
`JC_JNC4` varchar(5) DEFAULT NULL,
`WorkCodesNote4` varchar(300) DEFAULT NULL,
`ProjectCodesNote4` varchar(300) DEFAULT NULL,
`P_Num5` int(4) DEFAULT NULL,
`WONo5` int(11) DEFAULT NULL,
`WOLineNumber5` varchar(3) DEFAULT NULL,
`Customer5` varchar(50) DEFAULT NULL,
`Description5` varchar(1150) DEFAULT NULL,
`Hours5` decimal(4,2) DEFAULT NULL,
`WorkCodes5` varchar(10) DEFAULT NULL,
`JC_JNC5` varchar(5) DEFAULT NULL,
`WorkCodesNote5` varchar(300) DEFAULT NULL,
`ProjectCodesNote5` varchar(300) DEFAULT NULL,
`P_Num6` int(4) DEFAULT NULL,
`WONo6` int(11) DEFAULT NULL,
`WOLineNumber6` varchar(3) DEFAULT NULL,
`Customer6` varchar(50) DEFAULT NULL,
`Description6` varchar(1150) DEFAULT NULL,
`Hours6` decimal(4,2) DEFAULT NULL,
`WorkCodes6` varchar(10) DEFAULT NULL,
`JC_JNC6` varchar(5) DEFAULT NULL,
`WorkCodesNote6` varchar(300) DEFAULT NULL,
`ProjectCodesNote6` varchar(300) DEFAULT NULL,
`P_Num7` int(4) DEFAULT NULL,
`WONo7` int(11) DEFAULT NULL,
`WOLineNumber7` varchar(3) DEFAULT NULL,
`Customer7` varchar(50) DEFAULT NULL,
`Description7` varchar(1150) DEFAULT NULL,
`Hours7` decimal(4,2) DEFAULT NULL,
`WorkCodes7` varchar(10) DEFAULT NULL,
`JC_JNC7` varchar(5) DEFAULT NULL,
`WorkCodesNote7` varchar(300) DEFAULT NULL,
`ProjectCodesNote7` varchar(300) DEFAULT NULL,
`P_Num8` int(4) DEFAULT NULL,
`WONo8` int(11) DEFAULT NULL,
`WOLineNumber8` varchar(3) DEFAULT NULL,
`Customer8` varchar(50) DEFAULT NULL,
`Description8` varchar(1150) DEFAULT NULL,
`Hours8` decimal(4,2) DEFAULT NULL,
`WorkCodes8` varchar(10) DEFAULT NULL,
`JC_JNC8` varchar(5) DEFAULT NULL,
`WorkCodesNote8` varchar(300) DEFAULT NULL,
`ProjectCodesNote8` varchar(300) DEFAULT NULL,
`P_Num9` int(4) DEFAULT NULL,
`WONo9` int(11) DEFAULT NULL,
`WOLineNumber9` varchar(3) DEFAULT NULL,
`Customer9` varchar(50) DEFAULT NULL,
`Description9` varchar(1150) DEFAULT NULL,
`Hours9` decimal(4,2) DEFAULT NULL,
`WorkCodes9` varchar(10) DEFAULT NULL,
`JC_JNC9` varchar(5) DEFAULT NULL,
`WorkCodesNote9` varchar(300) DEFAULT NULL,
`ProjectCodesNote9` varchar(300) DEFAULT NULL,
`P_Num10` int(4) DEFAULT NULL,
`WONo10` int(11) DEFAULT NULL,
`WOLineNumber10` varchar(3) DEFAULT NULL,
`Customer10` varchar(50) DEFAULT NULL,
`Description10` varchar(1150) DEFAULT NULL,
`Hours10` decimal(4,2) DEFAULT NULL,
`WorkCodes10` varchar(10) DEFAULT NULL,
`JC_JNC10` varchar(5) DEFAULT NULL,
`WorkCodesNote10` varchar(300) DEFAULT NULL,
`ProjectCodesNote10` varchar(300) DEFAULT NULL,
`TechName` varchar(50) NOT NULL,
`OrderDate` date NOT NULL,
`Signature` blob,
`TotalHours` decimal(4,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
uj5u.com熱心網友回復：
varchar(21845)換成text，試下。uj5u.com熱心網友回復：
謝謝樓上的，但是還是同樣得報錯uj5u.com熱心網友回復：
你只發了一個表的建表陳述句，把其它的也一并發出來吧，有助于別人幫你轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/79530.html
標籤：MySQL
上一篇：【求進】MySQL 5.7+如何Where查找JSON陣列內物件的值
下一篇：欄位默認值