当前位置:主页>仓库管理软件> 列表

存储过程高手进 找免费档案管理软件

进销存软件版1楼: 最近这段时间偶在学习Delphi时,遇到了2个问题第1个问题是存储过程(做月报表用)代码如下,这段代码取得是系统时间比如2006年1月到3月的数据,我的想法是在Delphi中通过选择DateTimePicker控件来改变打印出来的年份和月份,比如2005年1月到2006年3月的数据,请大家发表下建议,谢谢了!

CREATE PROCEDURE [cbor].[cbda_jytjyue] AS
if exists (select * from sysobjects where id = object_id(N''[cbor].[cbda_jytjyf]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table cbor.cbda_jytjyf
create table cbor.cbda_jytjyf
(
ID numeric(10,0) identity not null,
month1 varchar(50) null,/*船舶类型*/
number float null,/*船艘数*/
ton float null,/*总吨位*/
price float null,/*成交总额*/

fwf float null,/*服务费*/
fwfbl varchar(10) null,/*服务费比例*/
pricebl varchar(10) null,/*服务费比例*/
tjrq varchar(10) null,/*统计日期——年、月*/

remark2 varchar(200) null,/*备注*/

primary key(ID)
)


/*向表中插入双内客户的*/
declare @tjrq varchar(20)

set @tjrq=CONVERT(varchar(20), YEAR(GETDATE())) + ''年'' + CONVERT(varchar(20),
MONTH(GETDATE())) + ''月''
insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''一月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=1) and (year(getdate())=year(cjrq))
/*向表中插入内->外客户的*/
insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''二月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=2) and (year(getdate())=year(cjrq))

/*向表中插入外->内客户的*/
insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''三月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=3) and (year(getdate())=year(cjrq))

/*向表中插入双外客户的*/
insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''四月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq


FROM cbor.cbda_jytj where (month(cjrq)=4) and (year(getdate())=year(cjrq))

insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''五月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=5) and (year(getdate())=year(cjrq))

insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''六月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=6) and (year(getdate())=year(cjrq))

insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''七月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=7) and (year(getdate())=year(cjrq))
insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''八月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=8) and (year(getdate())=year(cjrq))


insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''九月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=9) and (year(getdate())=year(cjrq))
insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''十月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=10) and (year(getdate())=year(cjrq))
insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''十一月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=11) and (year(getdate())=year(cjrq))
insert into cbdb.cbor.cbda_jytjyf(month1,number,ton,price,fwf,tjrq) SELECT ''十二月'',COUNT(*) AS number, SUM(zton) AS ton, SUM(price) AS price, SUM(fwf) AS fwf, @tjrq
FROM cbor.cbda_jytj where (month(cjrq)=12) and (year(getdate())=year(cjrq))

UPDATE cbor.cbda_jytjyf


SET fwfbl = suma, pricebl = sumb
FROM (SELECT SUM(fwf) AS suma, SUM(price) AS sumb
FROM cbor.cbda_jytjyf) a

GO
``````````````````````````````````````````````````````````````````````````
`````````````````````````````````````````````````````````````````````````````
第2个问题是点个增加按钮自动增加2006年的编号,不过进位有问题比如99→100这时候增加的编号就出问题了。
procedure Tkpxi_jydj.BitBtn1Click(Sender: TObject);
var
str,str1,stra:string;
aint:integer;
Qjudge:TADOQuery;
begin
stra:=formatdatetime(''yyyy'',now);
Qjudge:=TADOQuery.Create(self);
Qjudge.Connection:=FLServFrm.flservForm.ADO_db;
Qjudge.SQL.Add(''select * from cbda_jytj where substring(bh,1,4)=''''''+stra+'''''' order by cast(bh as int)'');
QJudge.Open;
QJudge.Last;

str:=QJudge.FieldByName(''bh'').asstring;
ADOQuery1.Append();
aint:=StrToInt(str)+1;//感觉是这里有问题

if not (adoquery1.State in [dsinsert,dsedit]) then adoquery1.Edit;
ADOQuery1.FieldByName(''bh'').Value:=stra+copy(IntToStr(aint),5,10);
ADOQuery1.FieldByName(''calc_type'').Value:=dbcombobox2.Text;
ADOQuery1.FieldByName(''calc_type'').Value:=DBLookupComboBox1.Text;
QJudge.Free;

2楼: 是不是我的表达不清楚啊,怎么没人发表下意见 如管家婆财务软件教程

3楼: 将年份和月份作为参数传给SP即可

4楼: 不知道你表中编号的方法是什么啊?
如果是要动态改变日期,存储过程要有参数传递吧.你的数据是不是明细是一张表,汇总数据是另一张表?

5楼: 在存储过程中加两个日期类型的参数,在dephi调用存储过程时将DateTimePicker中的值以字符串的方式传过去就可以了。

6楼: 楼主 你的代码太长了 大家都没精力去仔细看
你把有问题的代码精简出来 大家也好帮助你

进销存软件版7楼: 问题1:把存储过程中的getdate()换成新定义的时间变量,然后你从程序中传参数到存储过程中进行统计就可以了;
问题2:你的方法不差点劲,最起码也应该是把4位之后的字串去出来再进行+1运算,想你现在写的aint:=StrToInt(str)+1然后再把stra+copy(IntToStr(aint),5,10)写回数据库就显得烂了一点点,在最初查询的时候就应该把4位之后取出来保存到一个变量中,然后就对变量进行操作,这样就肯定不会出错了,尽可能不使用那么长的整数进行运算,容易出错的

8楼: 各位大哥,我刚接触存储过程,还不知道怎么写参数传到存储过程里面,麻烦知道的说下了,谢谢

9楼: CREATE PROCEDURE cbda_jytjyue (date1 DateTime,date2 DateTime)


as
....
SQL中调用为EXEC cbda_jytjyue 变量1,变量2

10楼: 续楼上.
比如用ADO
Adodataset.commandtext := ''EXEC cbda_jytjyue 变量1,变量2'';
adodataset.open;
就可以返回你要的数据集了.[:D]

11楼: 和两位楼上的
SQL存储过程
CREATE PROCEDURE a(@date1 DateTime,@date2 DateTime)
----------
delphi

Adodataset.commandtext := ''EXEC a @date1,@date2'';

12楼: 几位说的我都考虑过了,只是最关键的这句话不会改,本人有点笨,希望各位大哥说详细点了。
FROM cbor.cbda_jytj where (month(cjrq)=12) and (year(getdate())=year(cjrq)) 如免费档案管理软件

13楼: 存储过程居然可以写这么大
开始学习了

进销存软件版14楼: 建义如下
  1。CREATE PROCEDURE a(@date1 DateTime,@date2 DateTime)
2,在过程中不要建,可能定义表变量来处理
declare @table table
(
field1 fieldtype
....
)
这样速度更快,提高效率。
3.在过程最后加一条语句。select * from @table 可以返回数据
4.统计数据时可以不用一月一月去处理,可如下
    select month =
case month(cjrq)
when 1 then ''一月份''
where month(..) = @month
......
其中可以把求月份写一个函数去处理。

15楼: 在存储过程中定义参数:
CREATE procedure SP_GenBillNo
@sBranchCode varchar(30)
go
在DELPHI中传入参数到存储过程:
用ADOStoredProc1
ADOStoredProc1.Close;
ADOStoredProc1.Parameters.Refresh;
ADOStoredProc1.Parameters.ParamByName(''@Prefix'').Value := ''CJ'';
ADOStoredProc1.Parameters.ParamByName(''@stdate'').Value := FDateTime;
ADOStoredProc1.Parameters.ParamByName(''@numbercode'').Value := '' '';
ADOStoredProc1.Prepared;
ADOStoredProc1.ExecProc;

16楼: 能用存储过程的就用,
不方便用的话,就别强用,
倒头来,效率不说,耽误时间!!

17楼: 无论方便还是不方便 不知道的东西总是要学的

18楼: 最近手指卡门上了,昨天手指甲刚拔掉,真霉啊!!分发给大家了

19楼: 多人接受答案了。