DECLARE p_assis1 PROCEDURE FOR p_ass_orders
@m = :ls_maker,
@mn = :ls_makename,
@vid = :ls_vendorid,
@vname = :ls_vendorname,
@s_type = :ls_saletype,
@s_type1 = :ls_saletype1;
EXECUTE p_assis1;
if SQLCA.sqlcode = -1 then
MessageBox("错误信息", SQLCA.sqlerrtext)
else
messagebox("信息" , "制单成功!")CLOSE p_assis1;
@m = :ls_maker,
@mn = :ls_makename,
@vid = :ls_vendorid,
@vname = :ls_vendorname,
@s_type = :ls_saletype,
@s_type1 = :ls_saletype1;
EXECUTE p_assis1;
if SQLCA.sqlcode = -1 then
MessageBox("错误信息", SQLCA.sqlerrtext)
else
messagebox("信息" , "制单成功!")CLOSE p_assis1;
解决方案 »
- 时间段查询的问题。
- 关于一个销售财务系统方面的数据库设计问题请教?
- 挑战SQL大师级人物极限问题,望各位高手指点一下 怎么样写出这样的汇总语句出来 小弟感激不尽!!!!在线等!
- 如何将一列中的字符中的所有小写mm转成大写MM,关将所有X变成*号,谢谢。
- 在存储过程中设置SET ANSI_WARNINGS OFF出现的效率低下问题
- 简单问题
- SQL小白求教
- 最简单的错误
- 关于查询的问题?比较头疼
- 调研:多少人同意在大学期间打工,打工做什么最好。去网吧,工作室,买电脑,还是作家教
- 多表连接查询并汇总的SQL语句出现的奇怪问题,在线求教。
- 为什么,我在用实例,执行我的存储过程的时候老出先这个提示,错误,
、
以下是我的存储过程
if exists( select 1 from sysobjects where id = object_id( 'p_ass_orders') and type = 'P' )
drop procedure p_ass_orders
go/*制作总计定单*/create procedure p_ass_orders
@m char(6) ,
@mn varchar(12) ,
@vid char(8) ,
@vname varchar(30) ,
@oid char(12),
@s_type char(4),
@s_type1 char(2)
as
beginif @m = '' or @m is null
begin
raiserror( '传递参数错误!' , 16 , 1 )
return 0
end
declare @cnt int
select @cnt = count(*) from assistorder where maker = @m
print @cnt
if @cnt = 0 or @cnt is null
begin
raiserror( '没有需要制作的制单数据!' , 16 , 1 )
return 0
end
begin tran execute p_id 'order' , @oid output declare @ld_money decimal (15,4), @ld_quantity decimal (15,4)
select @ld_money = sum(quantity * price) from assistorder where maker = @m
insert into ordermain(orderid,vendorid,vendorname,operator,operatorname,maker,makername,makedate,deliverdate,contractid,saletype,moneyamt,killdate,status,flag,creator,createdate,re,deptid,gifts)
values( @oid , @vid , @vname , '' , '' , @m , @mn , getdate() , '' , @vid + @s_type1 , @s_type , @ld_money , null , '' ,'制作之中' , '' , getdate() , '' , '00', null) declare @sqlstr varchar(8000)
set @sqlstr='insert into orderdetail(orderid,comid,quantity,arrive,rawprice,purchasetaxrate,price,saleprice,warehouseid,warehouse,no,deptid)
select '''+cast(@oid as varchar)+''',assistorder.comid,sum(assistorder.quantity),'',plu.rawprice ,plu.purchasetaxrate ,assistorder.price , pluloction.saleprice ,assistorder.warehouseid ,warehouse.warehouse,'',00
from assistorder , plu , plulocation , warehouse
where (assistorder.comid = plu.comid) and (assistorder.comid = plulocation.comid) and (assistorder.warehouseid = warehouse.warehouseid) and (assistorder.deptid = plulocation.deptid)
group by assidtorder.comid,plu.rawprice ,plu.purchasetaxrate ,assistorder.price , plulocation.saleprice ,assistorder.warehouseid ,plulocation.warehouse' exec(@sqlstr) if @@error <> 0
begin
--print "执行失败!"
return 3
end
else
--print "执行成功!"
return 0end GO
他可能有多个值,我想将他都可以反回到,pb 中,不知道怎么做,
有知道的,可否,指点一下,非常感谢,
drop procedure p_ass_orders
go/*制作总计定单*/create procedure p_ass_orders
@m char(6) ,
@mn varchar(12) ,
@vid char(8) ,
@vname varchar(30) ,
@oid char(12),
@s_type char(4),
@s_type1 char(2)
as
begin
DECLARE @TABLE TABLE(ID INT NULL)------------
if @m = '' or @m is null
begin
raiserror( '传递参数错误!' , 16 , 1 )
return 0
end
declare @cnt int
select @cnt = count(*) from assistorder where maker = @m
print @cnt
if @cnt = 0 or @cnt is null
begin
raiserror( '没有需要制作的制单数据!' , 16 , 1 )
return 0
end
begin tran execute p_id 'order' , @oid output
INSERT INTO @TABLE (ID) VALUES(@oid )---------------------------
declare @ld_money decimal (15,4), @ld_quantity decimal (15,4)
select @ld_money = sum(quantity * price) from assistorder where maker = @m
insert into ordermain(orderid,vendorid,vendorname,operator,operatorname,maker,makername,makedate,deliverdate,contractid,saletype,moneyamt,killdate,status,flag,creator,createdate,re,deptid,gifts)
values( @oid , @vid , @vname , '' , '' , @m , @mn , getdate() , '' , @vid + @s_type1 , @s_type , @ld_money , null , '' ,'制作之中' , '' , getdate() , '' , '00', null) declare @sqlstr varchar(8000)
set @sqlstr='insert into orderdetail(orderid,comid,quantity,arrive,rawprice,purchasetaxrate,price,saleprice,warehouseid,warehouse,no,deptid)
select '''+cast(@oid as varchar)+''',assistorder.comid,sum(assistorder.quantity),'',plu.rawprice ,plu.purchasetaxrate ,assistorder.price , pluloction.saleprice ,assistorder.warehouseid ,warehouse.warehouse,'',00
from assistorder , plu , plulocation , warehouse
where (assistorder.comid = plu.comid) and (assistorder.comid = plulocation.comid) and (assistorder.warehouseid = warehouse.warehouseid) and (assistorder.deptid = plulocation.deptid)
group by assidtorder.comid,plu.rawprice ,plu.purchasetaxrate ,assistorder.price , plulocation.saleprice ,assistorder.warehouseid ,plulocation.warehouse' exec(@sqlstr) if @@error <> 0
begin
--print "执行失败!"
return 3
end
else
--print "执行成功!"
return 0
SELECT * FROM @TABLE ---------------
end GO有DATAWINDOW(用过程做数据源)
可否说详细点啊,
我本事想,在让存储过程返回@oid这个值,在pb中接收到,
让后,让他显示在一个数据窗口中啊,
、你的这个,是建一个临时表吗,,
DECLARE @TABLE TABLE(ID INT NULL)------------
end 那我在pb中,怎么得到这个值拉,
你这个,没得到我想要的值拉,我想要返回@oid,拉,
ls_maker,ls_makename,ls_vendorid,ls_vendorname,ls_saletype,ls_saletype1
这些,是我要传到存储过程的一些变量啊,
DECLARE p_assis1 PROCEDURE FOR p_ass_orders
@m = :ls_maker,
@mn = :ls_makename,
@vid = :ls_vendorid,
@vname = :ls_vendorname,
@s_type = :ls_saletype,
@s_type1 = :ls_saletype1
@oid = :ls_oid; -------------------------------
EXECUTE p_assis1;
fetch p_assis1 into ls_oid ; -------------------------
if SQLCA.sqlcode = -1 then
MessageBox("错误信息", SQLCA.sqlerrtext)
else
messagebox("信息" , "制单成功!")CLOSE p_assis1;
,那如果我有多个值拉,,我不知道怎么半拉,
你这个能返回吗,我建一个datawindow,,可是datawindow object 都没有啊,
可是这几个是我从pb中传过去的值啊,
你说的,,用数据源,p_ass_orders
我还是不明白,
注意:过程做数据源时应自动提交,不然会锁表
为什么,,
对我这个过程而言,本来,我的要求是做一张单的就是返回一个oid,以前可以实现,但是,现在为什么产生了三个oid啊,那闷,啊,
if exists( select 1 from sysobjects where id = object_id( 'p_ass_orders') and type = 'P' )
drop procedure p_ass_orders
go/*制作总计定单*/create procedure p_ass_orders
@m char(6) ,
@mn varchar(12) ,
@vid char(8) ,
@vname varchar(30) ,
@s_type char(4),
@s_type1 char(2)as
begin
create table #t1
(
orderid char(12) not null
)
if @m = '' or @m is null
begin
raiserror( '传递参数错误!' , 16 , 1 )
return 0
end
declare @cnt char(12) , @oid char(12)
select @cnt = count(*) from assistorder where maker = @m
if @cnt = 0 or @cnt is null
begin
raiserror( '没有需要制作的制单数据!' , 16 , 1 )
return 0
end
begin tran
execute p_id 'order' , @oid output
declare @ld_money decimal (15,4) , @ld_quantity decimal (15,4)
select @ld_money = sum(quantity * price) from assistorder where maker = @m insert into ordermain(orderid,vendorid,vendorname,operator,operatorname,maker,makername,makedate,deliverdate,contractid,saletype,moneyamt,killdate,status,flag,creator,createdate,re,deptid,gifts)
values( @oid , @vid , @vname , '' , '' , @m , @mn , getdate() , '' , @vid + @s_type1 , @s_type , @ld_money , null , '' ,'制作之中' , '' , getdate() , '' , '00', null)
insert into orderdetail(orderid,comid,quantity,arrive,rawprice,purchasetaxrate,price,saleprice,warehouseid,warehouse,no,deptid)
select @oid,assistorder.comid,sum(assistorder.quantity),0.00 ,plu.rawprice ,plu.purchasetaxrate ,assistorder.price , assistorder.saleprice ,'00' , '总部' ,'', '00'
from assistorder , plu
where assistorder.comid = plu.comid
group by assistorder.comid,plu.rawprice ,plu.purchasetaxrate ,assistorder.price , assistorder.saleprice
if @@error <> 0 or @@rowcount = 0
begin
rollback tran
return -1
end
insert into #t1 (orderid) select @oid
commit tran
delete from assistorder where maker = @m
select orderid from #t1
return 0
end GO
需要在过程中,先建一个临时表,将数据加到这个表中,
再在pb中,retrieve(参数1,参数2。)