drop procedure if exists proc_novelSearch; create procedure proc_novelSearch( in searchType varchar(20), -- 搜索类型 in searchValue varchar(40), -- 搜索值 in pageItem int, -- 页项 in pageIndex int -- 页码 ) begin declare sqlStr varchar(250); set sqlStr = concat('select * from (select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel where tempNovel.Id not in (select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')'); prepare stmtNovelSearch from sqlStr; execute stmtNovelSearch; deallocate prepare stmtNovelSearch; end;
还是不行啊!drop procedure if exists proc_novelSearch; delimiter // create procedure proc_novelSearch( in searchType varchar(20), -- 搜索类型 in searchValue varchar(40), -- 搜索值 in pageItem int, -- 页项 in pageIndex int -- 页码 ) begin declare sqlStr varchar(250); set sqlStr = concat('select * from (select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel where tempNovel.Id not in(select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')'); prepare stmtNovelSearch from sqlStr; execute stmtNovelSearch; deallocate prepare stmtNovelSearch; end; // delimiter ;提示: 错误代码: 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 'delimiter // create procedure proc_novelSearch( in searchType varchar(20),' at line 1 (耗费 0 ms)错误代码: 1193 Unknown system variable 'sqlStr' (耗费 0 ms)错误代码: 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 'sqlStr' at line 1 (耗费 16 ms)错误代码: 1243 Unknown prepared statement handler (stmtNovelSearch) given to EXECUTE (耗费 0 ms)错误代码: 1243 Unknown prepared statement handler (stmtNovelSearch) given to DEALLOCATE PREPARE (耗费 0 ms)错误代码: 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 'end' at line 1 (耗费 0 ms)错误代码: 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 '//' at line 1 (耗费 0 ms)
delimiter // drop procedure if exists proc_novelSearch;// create procedure proc_novelSearch( in searchType varchar(20), -- 搜索类型 in searchValue varchar(40), -- 搜索值 in pageItem int, -- 页项 in pageIndex int -- 页码 ) begin declare sqlStr varchar(250); set sqlStr = concat('select * from (select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel where tempNovel.Id not in(select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')'); prepare stmtNovelSearch from sqlStr; execute stmtNovelSearch; deallocate prepare stmtNovelSearch; end; // delimiter ;
drop procedure if exists proc_novelSearch; delimiter // create procedure proc_novelSearch( in searchType varchar(20), -- 搜索类型 in searchValue varchar(40), -- 搜索值 in pageItem int, -- 页项 in pageIndex int -- 页码 ) begin -- declare sqlStr varchar(250); set @sqlStr = concat('select * from (select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel where tempNovel.Id not in(select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')'); prepare stmtNovelSearch from @sqlStr; execute stmtNovelSearch; deallocate prepare stmtNovelSearch; end; // delimiter ;
drop procedure if exists proc_novelSearch; delimiter // create procedure proc_novelSearch( in searchType varchar(20), -- 搜索类型 in searchValue varchar(40), -- 搜索值 in pageItem int, -- 页项 in pageIndex int -- 页码 ) begin set @sqlStr = concat('select * from (select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel where tempNovel.Id not in(select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')'); PREPARE stmtNovelSearch FROM @sqlStr; execute stmtNovelSearch; deallocate prepare stmtNovelSearch; end; // delimiter ;PREPARE 有一些特殊性,需要用连接给变量。
drop procedure if exists proc_novelSearch;
create procedure proc_novelSearch(
in searchType varchar(20), -- 搜索类型
in searchValue varchar(40), -- 搜索值
in pageItem int, -- 页项
in pageIndex int -- 页码
)
begin
declare sqlStr varchar(250);
set sqlStr = concat('select * from
(select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel
where tempNovel.Id not in
(select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')');
prepare stmtNovelSearch from sqlStr;
execute stmtNovelSearch;
deallocate prepare stmtNovelSearch;
end;
delimiter //
create procedure proc_novelSearch(
in searchType varchar(20), -- 搜索类型
in searchValue varchar(40), -- 搜索值
in pageItem int, -- 页项
in pageIndex int -- 页码
)
begin
declare sqlStr varchar(250);
set sqlStr = concat('select * from (select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel where tempNovel.Id not in(select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')');
prepare stmtNovelSearch from sqlStr;
execute stmtNovelSearch;
deallocate prepare stmtNovelSearch;
end;
//
delimiter ;提示:
错误代码: 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 'delimiter //
create procedure proc_novelSearch(
in searchType varchar(20),' at line 1
(耗费 0 ms)错误代码: 1193
Unknown system variable 'sqlStr'
(耗费 0 ms)错误代码: 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 'sqlStr' at line 1
(耗费 16 ms)错误代码: 1243
Unknown prepared statement handler (stmtNovelSearch) given to EXECUTE
(耗费 0 ms)错误代码: 1243
Unknown prepared statement handler (stmtNovelSearch) given to DEALLOCATE PREPARE
(耗费 0 ms)错误代码: 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 'end' at line 1
(耗费 0 ms)错误代码: 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 '//' at line 1
(耗费 0 ms)
delimiter //
drop procedure if exists proc_novelSearch;//
create procedure proc_novelSearch(
in searchType varchar(20), -- 搜索类型
in searchValue varchar(40), -- 搜索值
in pageItem int, -- 页项
in pageIndex int -- 页码
)
begin
declare sqlStr varchar(250);
set sqlStr = concat('select * from (select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel where tempNovel.Id not in(select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')');
prepare stmtNovelSearch from sqlStr;
execute stmtNovelSearch;
deallocate prepare stmtNovelSearch;
end;
//
delimiter ;
delimiter //
create procedure proc_novelSearch(
in searchType varchar(20), -- 搜索类型
in searchValue varchar(40), -- 搜索值
in pageItem int, -- 页项
in pageIndex int -- 页码
)
begin
-- declare sqlStr varchar(250);
set @sqlStr = concat('select * from (select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel where tempNovel.Id not in(select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')');
prepare stmtNovelSearch from @sqlStr;
execute stmtNovelSearch;
deallocate prepare stmtNovelSearch;
end;
//
delimiter ;
delimiter //
create procedure proc_novelSearch(
in searchType varchar(20), -- 搜索类型
in searchValue varchar(40), -- 搜索值
in pageItem int, -- 页项
in pageIndex int -- 页码
)
begin
set @sqlStr = concat('select * from (select * from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*pageIndex,') as tempNovel where tempNovel.Id not in(select Id from Novel where ',searchType,' like (%',searchValue,'%) limit 0,',pageItem*(pageIndex-1),')');
PREPARE stmtNovelSearch FROM @sqlStr;
execute stmtNovelSearch;
deallocate prepare stmtNovelSearch;
end;
//
delimiter ;PREPARE 有一些特殊性,需要用连接给变量。