过程如下
create procedure Gen_Id @genname varchar(20)
as
begin
declare @newid int
begin transaction
update generators set lastid=lastid+1 where genname=@genname
select @newid=lastid from generators where genname=@genname
if @@rowcount=1
begin
update lastinsertid set id=@newid
commit transaction
return @newid //返回值
end
else begin
rollback transaction
return -6 //出错,则返回-6
end
endGO要取得过程的返回值,代码如下
function TSQLServerSession.NewSerialNo(const GenID: string): integer;
begin
with FStoreProc do
begin
ProcedureName:='Gen_Id';
//先创建字段
Parameters.Clear;
Parameters.CreateParameter('@genname', ftString, pdInput , 20, GenID); //输入
Parameters.ParamByName('@genname').Value:= GenID;
Prepared:=true;
ExecProc;
if Eof then
Result:=Parameters[0].Value //传入Gen_opid ,为什么返回也是Gen_opid呢????
else Result:=0;
if Result<=0 then
raise Exception.Create('生成序号失败,无此序号标记:'+GenID);
end;
end;
create procedure Gen_Id @genname varchar(20)
as
begin
declare @newid int
begin transaction
update generators set lastid=lastid+1 where genname=@genname
select @newid=lastid from generators where genname=@genname
if @@rowcount=1
begin
update lastinsertid set id=@newid
commit transaction
return @newid //返回值
end
else begin
rollback transaction
return -6 //出错,则返回-6
end
endGO要取得过程的返回值,代码如下
function TSQLServerSession.NewSerialNo(const GenID: string): integer;
begin
with FStoreProc do
begin
ProcedureName:='Gen_Id';
//先创建字段
Parameters.Clear;
Parameters.CreateParameter('@genname', ftString, pdInput , 20, GenID); //输入
Parameters.ParamByName('@genname').Value:= GenID;
Prepared:=true;
ExecProc;
if Eof then
Result:=Parameters[0].Value //传入Gen_opid ,为什么返回也是Gen_opid呢????
else Result:=0;
if Result<=0 then
raise Exception.Create('生成序号失败,无此序号标记:'+GenID);
end;
end;
解决方案 »
- 如何根据窗口句柄得到应用程序的绝对路径?
- 高分求ado控件的数据字典的实现(up有分,先给100分,若完美可用可以加到2000分)
- Gauss-Jordan Method
- 如何用SQL格式化一个IP地址?我得到的IP地址是一串数字,想显示为255.255.255.255那样的样子。
- 关于在table内杂擦插入一条记录! online !
- listview 颜色控制问题,欢迎大家讨论,包括treeview,stringview
- 关于局部窗体变量的释放问题?
- 一道公司的面试题:
- 实在是无法可想了,这段代码也会错!!!???高分喽!!
- Delphi中 "@" 符号是什么意思? 请举个例子
- 数据库写入数据和读出数据的时候怎么样防止冲突
- webbrowser遍历Form的问题
function TSQLServerSession.NewSerialNo(const GenID: string): integer;
begin
SQLStoredProc1.Close;
SQLStoredProc1.StoredProcName:='Gen_Id';
SQLStoredProc1.ParamByName('@genname').Clear;
SQLStoredProc1.ParamByName('@genname').AsString:=GenID;
SQLStoredProc1.Prepared:=true;
SQLStoredProc1.ExecProc;
if SQLStoredProc1.Eof then Result:=SQLStoredProc1.Params[0].AsInteger
else Result:=0;
if Result<=0 then
raise Exception.Create('生成序号失败,无此序号标记:'+GenID);
end;
as
begin
declare @newid int
.................
commit transaction
select @newid //返回值
end
else begin
rollback transaction
select -6
end然后 ADOQUERY1.SQL.TEXT:= 'exec Gen_Id '''+GenID+'''';
ADOQUERY1.OPEN;
Result:= ADOQUERY1.FIELDS[0].ASINTEGER;
把open改成active时,ADOQUERY1.eof=true
没有到达想要的结果
GO
/****** Object: UserDefinedFunction [dbo].[Get_Year_Days] Script Date: 08/30/2010 08:12:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Get_Year_Days]
(
@ns int
)
RETURNS int
AS
BEGIN
RETURN
case
when @ns>=1 and @ns<10 then 5
when @ns>=10 and @ns<=19 then 10
when @ns>19 and @ns<=20 then 15
when @ns>20 then 20
else 0
end
END2.sql存儲過程:USE [MES]
GO
/****** Object: StoredProcedure [dbo].[GetBmbhsByFzr] Script Date: 08/30/2010 08:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetBmbhsByFzr]
@fzr nvarchar(10)
AS
BEGIN
SET NOCOUNT ON;
declare @a varchar(2000)
declare @b varchar(2000)
declare @c varchar(2000)
declare @d varchar(2000)
set @a=''
set @b=''
set @c=''
set @d=''
select @a=@a+convert(varchar(20),bmbh)+',',@b=@b+bmjb+',',@c=@c+bmmc+',',@d=@d+convert(varchar(20),max(sjbm))+',' from RES_DEPARTMENT where fzr=@fzr group by bmbh,bmjb,bmmc,sjbm
if LEN(@a)>1
set @a=LEFT(@a,LEN(@a)-1)
if LEN(@b)>1
set @b=LEFT(@b,LEN(@b)-1)
if LEN(@c)>1
set @c=LEFT(@c,LEN(@c)-1)
if LEN(@d)>1
set @d=LEFT(@d,LEN(@d)-1)
select @a as bmbh,@b as bmjb,@c as bmmc,@d as sjbm
END
估计是你前一种用法不对,Prepared:=true;这句注释掉看看
function TSQLServerSession.NewSerialNo(const GenID: string): integer;
begin
with FStoreProc do
begin
ProcedureName:='Gen_Id';
//先创建字段
Parameters.Clear;
Parameters.CreateParameter('ReturnValue',ftInteger,pdReturnValue,10,NULL);//加這行代碼返回值的參數
Parameters.CreateParameter('@genname', ftString, pdInput , 20, GenID); //输入
Parameters.ParamByName('@genname').Value:= GenID;
Prepared:=true;
ExecProc;
if Eof then
Result:=Parameters[0].Value //传入Gen_opid ,为什么返回也是Gen_opid呢????
else Result:=0;
if Result<=0 then
raise Exception.Create('生成序号失败,无此序号标记:'+GenID);
end;
end;
Parameters.CreateParameter('ReturnValue',ftInteger,pdReturnValue,10,NULL);//加這行代碼返回值的參數加这行就报错,提示太多参数了
PROCEDURE 和 FUNCTION ........