--------------Delphi中调用存储过程mywage---------------
with form1.adostoredproc1 do
begin
close;
procedurename:='mywage';
parameters.Clear;
parameters.CreateParameter('@lnmonth',ftInteger,pdinput,8,0);
Parameters.ParamByName('@lnmonth').Value :=mm-1;
try
open;
except
Application.MessageBox('连接数据库失败','系统提示',mb_OK);
Application.Terminate;
end
end;----------------存储过程:mywage--------------------------
CREATE PROCEDURE mywage @lnmonth int AS
select a.nameid,a.name,(year(getdate())-year(a.workage)+1)*5 as gl,b.dagz,b.gjj,b.ydj,
case a.rank
when '技术员' then 20
when '助工' then 50
when '工程师' then 80
when '高工' then 100
end as zz
from human a left join wage b on a.nameid=b.nameid and b.wagemonth=@lnmonth
GO
with form1.adostoredproc1 do
begin
close;
procedurename:='mywage';
parameters.Clear;
parameters.CreateParameter('@lnmonth',ftInteger,pdinput,8,0);
Parameters.ParamByName('@lnmonth').Value :=mm-1;
try
open;
except
Application.MessageBox('连接数据库失败','系统提示',mb_OK);
Application.Terminate;
end
end;----------------存储过程:mywage--------------------------
CREATE PROCEDURE mywage @lnmonth int AS
select a.nameid,a.name,(year(getdate())-year(a.workage)+1)*5 as gl,b.dagz,b.gjj,b.ydj,
case a.rank
when '技术员' then 20
when '助工' then 50
when '工程师' then 80
when '高工' then 100
end as zz
from human a left join wage b on a.nameid=b.nameid and b.wagemonth=@lnmonth
GO
将ADOStoredProc的connection属性设置为你所用的数据源,
将ADOStoredProc的ProcedureName属性设置为你要调用的存储过程名,
ADOStoredProc空件自动会读入存储过程的所有参数,一般不需要设置,
这是最方便的方法,如果你不是用ADO用BDE的方法差不多。
function TfrmSiteManage.wjConvertPCCIDtoName(theID: string): string;
begin
with DM.ADOStoredProc do begin
ProcedureName:='wj_PCCIDtoName'; //将ID号转化为相应的省/地/县名称
Parameters.Clear ;
with Parameters.AddParameter do begin
Name:='@ID';
DataType:=ftString;
Direction:=pdInput;
Value:=theID; //设置输入值,如果用ID作此变量名则会出错
end;
with Parameters.AddParameter do begin //输出参数设置
Name:='@Name';
DataType:=ftString;
Size:=20;
Direction:=pdOutput;
end;
execproc; //不返回结果集时用ExecProc,返回结果集时用Open
Result :=VartoStr(Parameters.ParamValues['@Name']);
end;
end;SQL SERVER 2000中的存储进程如下:
CREATE PROCEDURE dbo.wj_PCCIDtoName @ID varchar(6),@Name nvarchar(20) OUTPUT
AS
--@ID为需要检索的ID号,@Name为返回的名字
if (Select Count(*) From tdsPCC Where PCC_ID=@ID)=0
BEGIN
PRINT '没有这个号码,请检查。'
RETURN(1)
END
ELSE
BEGIN
Select @Name=PCCName From tdsPCC Where PCC_ID=@ID
RETURN(0)
END
GO