create procedure addperson
(@id int)
as
if exists(select 1 from A91 where A018 in(select top 1 唯一号 from person where 人员类别='正式工'))
insert into A91 select @id
else
insert into A99 select @id
go
(@id int)
as
if exists(select 1 from A91 where A018 in(select top 1 唯一号 from person where 人员类别='正式工'))
insert into A91 select @id
else
insert into A99 select @id
go
create procedure addperson
(@id int,@class nvarchar(10))
as
if exists(select 1 from A91 where A018 in(select top 1 唯一号 from person where 人员类别='正式工'))
begin
if @class='正式工'
insert into A91 select @id
else
insert into A99 select @id
end
else
if @class='正式工'
insert into A99 select @id
else
insert into A91 select @id
go
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE my_sql
AS
declare @bm0000 varchar(2),@tablename varchar(20)
declare fox cursor for select top 50 bm0000,tablename from bm_kk
open fox
fetch next from fox
into @bm0000,@tablename
while @@fetch_status=0
begin
insert into @tablename(a0188)select a0188 from a01 where a0191=@bm0000 and
a0188 not in(select a0188 from @tablename)
into @bm0000,@tablename
end
close fox deallocate foxGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO这个游标基本上可以解决
但提示没声明变量,对游标一知半解?请指点下?谢谢
GO
/****** Object: StoredProcedure [dbo].[SP_WKO_SCHEDULE] Script Date: 11/25/2008 09:15:29 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_WKO_SCHEDULE] AS
delete from WKO_SCHEDULE where dateadd(dd,60,ImportDate)<=getdate()
delete from WKO_SCHEDULE where (WKO_XB is null)and(WKO_BC is null)and(WKO_HS is null) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ttt] select * into ttt
from
(
select *,case IsSMT when 1 then NHR*WKO_QTY else NHR*WKO_QTY/BZRS end as womenyaode
from
(
select *,case isnull(TT,0) when 0 then WKO_TIMES/WKO_QTY else TT end as NHR,case isnull(RS,0) when 0 then 25 else RS end as BZRS
from
(
select *,
case WKO_TIMES when WKO_TIMES then (select NewHR/1000 from PRO_QUANTITY a where PN=WKO_ITEMNO and GX=WKO_GX and CBZX=WKO_CBZX) end as TT,
(select BZRS from PRO_QUANTITY where PN=WKO_ITEMNO and GX=WKO_GX and CBZX=WKO_CBZX) AS RS,case when WKO_CBZX in (select distinct CBZX from RES_XB where DomainType=1) then 1 else 0 end as IsSMT
from TMP_WKO_ORDER
) f where WKO_TYPE<>'WO' and WKO_TYPE<>'WW' and WKO_STATUS<91
)_f
)_ff where WKO_TIMES is not null
DECLARE @WKO_STATUS NVARCHAR(100),@WKO_ITEMNO NVARCHAR(100),@WKO_TYPE NVARCHAR(100),@WKO_DESC NVARCHAR(100),@WKO_BZRS NVARCHAR(100),@WKO_PLANER NVARCHAR(100),@WKO_QTY INT,@WKO_CBZX NVARCHAR(100),@WKO_GX INT ,@WKO_TIMES FLOAT,@WKO_ODATE NVARCHAR(100),@WKO_HS FLOAT ,@WKO_MOVECL FLOAT,@STATUS int
DECLARE @WKO_ORDERNO NVARCHAR(100)
declare Searchcursor cursor for select WKO_STATUS,WKO_ITEMNO,WKO_TYPE,WKO_DESC,RS,WKO_PLANNER,WKO_QTY,WKO_CBZX,WKO_GX,NHR,WKO_ODATE,womenyaode,WKO_ORDERNO from ttt
open Searchcursor
fetch next from Searchcursor into @WKO_STATUS,@WKO_ITEMNO,@WKO_TYPE,@WKO_DESC,@WKO_BZRS,@WKO_PLANER,@WKO_QTY,@WKO_CBZX,@WKO_GX,@WKO_TIMES,@WKO_ODATE,@WKO_HS,@WKO_ORDERNO
while(@@fetch_status=0)
begin
--print('前,WO:'+@WKO_ORDERNO+',GX:'+convert(nvarchar(20),@WKO_GX)+',CBZX:'+@WKO_CBZX)
if not exists(select * from WKO_BACK_SCHEDULE where WKO_ORDERNO=@WKO_ORDERNO and WKO_GX=@WKO_GX)
begin
--print('后,WO:'+@WKO_ORDERNO+',GX:'+convert(nvarchar(20),@WKO_GX)+',CBZX:'+@WKO_CBZX)
if not exists(select * from WKO_SCHEDULE where WKO_ORDERNO=@WKO_ORDERNO and WKO_GX=@WKO_GX and WKO_CBZX=@WKO_CBZX)
begin
EXEC SP_WKO_JPJJCL @WKO_ITEMNO,@WKO_GX,@WKO_CBZX,@Result=@WKO_MOVECL OUTPUT
INSERT INTO WKO_SCHEDULE(WKO_STATUS,WKO_ITEMNO,WKO_TYPE,WKO_DESC,WKO_BZRS,WKO_PLANER,WKO_QTY,WKO_CBZX,WKO_GX,WKO_TIMES,WKO_ODATE,WKO_HS,WKO_ORDERNO,WKO_MOVECL)
VALUES(@WKO_STATUS,@WKO_ITEMNO,@WKO_TYPE,@WKO_DESC,@WKO_BZRS,@WKO_PLANER,@WKO_QTY,@WKO_CBZX,@WKO_GX,@WKO_TIMES,@WKO_ODATE,@WKO_HS,@WKO_ORDERNO,@WKO_MOVECL)
end
end else
begin
update WKO_SCHEDULE set WKO_STATUS=@WKO_STATUS where WKO_ORDERNO=@WKO_ORDERNO and WKO_GX=@WKO_GX and WKO_CBZX=@WKO_CBZX
end
fetch next from Searchcursor into @WKO_STATUS,@WKO_ITEMNO,@WKO_TYPE,@WKO_DESC,@WKO_BZRS,@WKO_PLANER,@WKO_QTY,@WKO_CBZX,@WKO_GX,@WKO_TIMES,@WKO_ODATE,@WKO_HS,@WKO_ORDERNO
end
close Searchcursor
deallocate Searchcursor
drop table [dbo].[ttt]
asdeclare @tmpid varchar(50),@tmptype varchar(50)declare tmpcursor cursor for
select distinct(人员类别) from person
where person.人员类别 in (select 描述 from lx)fetch tmpcursor into @tmpid
while @@fetch_status=0
begin
//生成表名
declare @random decimal(19,17),@random_id int,@tablename varchar(30),@sql varchar(200)
set @random =RAND()
set @random_id=convert(smallint,right(convert(varchar(20),@random),3))
set @tablename = 'A'+cast(@random_id as varchar(5)
while exists(select * from lx where 表名=@tablename)
begin
set @random =RAND()
set @random_id=convert(smallint,right(convert(varchar(20),@random),3))
set @tablename = 'A'+cast(@random_id as varchar(5)
end
insert into lx values(@tmpid,@tablename)
set @sql ='create table '+@tablename+' (唯一名 int)'
exec (@sql)
fetch tmpcursor into @tmpid
end
close tmpcursor
deallocate tmpcursordeclare tmpcursor1 cursor for select 唯一名,人员类别 from person
open tmpcursor1
fetch tmpcursor1 into @tmpid,@tmptype
while @@Fetch_status=0
begin
select @tablename=表名 from lx where 描述=@tmptype
if not exists(select * from @tablename where 唯一名=@tmpid)
begin
set @sql = 'insert into '+@tablename+' values('+@tmpid+')'
exec (@sql)
end
fetch tmpcursor1 into @tmpid,@tmptype
end
close tmpcursor1
deallocate tmpcursor1//最好在触发器中写这些代码,要不然每次都要将所有数据检查一次
ALTER PROCEDURE my_sql
AS
declare @bm0000 varchar(2),@tablename varchar(20),@sql varchar(8000)
declare fox cursor for select top 50 bm0000,tablename from bm_kk
open fox
fetch next from fox into @bm0000,@tablename
while @@fetch_status=0
begin
set @sql='insert into '+@tablename+'(a0188) select a0188 from a01 where a0191='+@bm0000
+' and a0188 not in(select a0188 from '+@tablename
exec(@sql) fetch next from fox into @bm0000,@tablename
end close fox
deallocate fox
GO