我有一个存储过程如下:
CREATE PROCEDURE up_mysqltestuser_SELECT_PageAble
(
_WhereClause VARCHAR(2000),
_OrderBy VARCHAR(2000)
)
begin
CREATE TEMPORARY TABLE _TempUseridTable
(
userid INT
);
-- 下面想调用别的存储过程把返回的记录集插入当前的临时表_TempUseridTable
-- 类似 INSERT INTO _TempuseridTable(userid ) xxxxxx...........DROP TABLE _TempuseridTable;end;问题是.xxxx 怎么写?
我试过用
INSERT INTO _TempuseridTable(userid )
call otherstoredprocedure(_WhereClause,_OrderBy);
不行
CREATE PROCEDURE up_mysqltestuser_SELECT_PageAble
(
_WhereClause VARCHAR(2000),
_OrderBy VARCHAR(2000)
)
begin
CREATE TEMPORARY TABLE _TempUseridTable
(
userid INT
);
-- 下面想调用别的存储过程把返回的记录集插入当前的临时表_TempUseridTable
-- 类似 INSERT INTO _TempuseridTable(userid ) xxxxxx...........DROP TABLE _TempuseridTable;end;问题是.xxxx 怎么写?
我试过用
INSERT INTO _TempuseridTable(userid )
call otherstoredprocedure(_WhereClause,_OrderBy);
不行
_WhereClause VARCHAR(2000),
_OrderBy VARCHAR(2000)
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
DECLARE _sql VARCHAR(8000);
SET _sql = 'SELECT
userid,
name,
chinesename,
registerdatetime,
jf,
description
FROM dbo.mysqltestuser
'; IF (not isnull(_WhereClause)) AND (_WhereClause<>'') then
SET _sql = concat(_sql , ' WHERE ' , _WhereClause);
end if;
IF (not isnull(_OrderBy)) AND ( _OrderBy<>'') then
SET _sql = _sql + ' ORDER BY ' + _OrderBy;
end if;
execute _sql;
end;
CREATE PROCEDURE `up_mysqltestuser_SELECT_Getuserid`(
_WhereClause VARCHAR(2000) ,
_OrderBy VARCHAR(2000)
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
DECLARE _sql VARCHAR(8000);
SET _sql = 'SELECT
userid
FROM mysqltestuser
';
IF (not isnull(_WhereClause)) AND (_WhereClause <> '') then
SET _sql = _sql + ' WHERE ' + _WhereClause;
END if;
IF (NOT isNULL(_OrderBy)) AND (_OrderBy <>'') then
SET _sql = _sql + ' ORDER BY ' + _OrderBy;
END IF;
execute _sql;
end;
简写如下,楼主自己去翻译吧。(以下语句不能直接拿到mysql中去执行的)
已经定义存储过程
CREATE PROCEDURE myfirstproc(
in1 VARCHAR(2000) ,
in2 VARCHAR(2000) ,
out out1 VARCHAR(2000) ,
inout inout1 VARCHAR(2000) ,
)
存储体
set out1 = concat(in1,int2,'ss');
set inout1 = "io";
另一个存储过程存储体:
declare a VARCHAR(2000);
declare b VARCHAR(2000);
declare c VARCHAR(2000);
declare d VARCHAR(2000);set a="b";
set b="c";
set d="e" ;
call myfirstproc(a,b,c,d);
select a,b,c,d;将出现如下结果集:
a b c d <-- 本行是select的标题 b c bcss io <-- 本行是select的内容楼主会了吗?