28,390
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE dbo.getUserInfo
@userid int,
as
set nocount on
begin
select usertel,usermail
from dbo.[userinfo]
where userid=@userid
end
go
DIM checklg,UserID,UserName,UserTel,UserMail
DIM MyComm,MyRst
UserID = 1
Set MyComm = Server.CreateObject("ADODB.Command")
with MyComm
.ActiveConnection = sqlConn 'sqlConn是数据库连接字串
.CommandText = "getUserInfo" '指定存储过程名
.CommandType = 4 '表明这是一个存储过程
.Prepared = true '要求将SQL命令先行编译
.Parameters.append .CreateParameter("@userid",3,1,4,UserID)
Set MyRst = .Execute
end with
Set MyComm = Nothing
'if not MyRst is Nothing then
'Set MyRst = MyRst.NextRecordset()
UserTel = MyRst(0)
UserMail = MyRst(1)
response.Write UserTel
'end if
Set MyRst = Nothing
CREATE PROCEDURE dbo.getUserInfo
@userid int,
as
set nocount on
begin
select usertel,usermail
from dbo.[userinfo]
where userid=@userid
end
go
DIM checklg,UserID,UserName,UserTel,UserMail
DIM MyComm,MyRst
UserID = 1
Set MyComm = Server.CreateObject("ADODB.Command")
with MyComm
.ActiveConnection = MyConStr 'MyConStr是数据库连接字串
.CommandText = "getUserInfo" '指定存储过程名
.CommandType = 4 '表明这是一个存储过程
.Prepared = true '要求将SQL命令先行编译
.Parameters.append .CreateParameter("@userid",3,1,4,UserID)
Set MyRst = .Execute
end with
Set MyComm = Nothing
if not MyRst is Nothing then
Set MyRst = MyRst.NextRecordset()
UserTel = MyRst(0)
UserMail = MyRst(1)
end if
Set MyRst = Nothing
CREATE PROCEDURE [dbo].[Find_WorkProduct]
@wlph varchar(20),@cc int,@rsjt int
as
set nocount on
declare @xx int,@che int,@xx1 int,@gs numeric(16,6),@jjgs numeric(16,6),@rs numeric(16,6)
set @xx=1
Create table #work(order_jl varchar(250),strGysx Varchar(10),che int,strgybh varchar(50),strcpgyh varchar(50), gs numeric(16,4),jjgs numeric(16,4),rs numeric(16,2),cr_na varchar(20),cr_dae datetime)
Create table #work1(m_key int IDENTITY (1, 1) NOT NULL,order_jl varchar(250),strGysx Varchar(10),che int,strgybh varchar(50),strcpgyh varchar(50),strgymq Varchar(100), gs numeric(16,4),jjgs numeric(16,4),rs numeric(16,2),strbb varchar(10),gzgroup varchar(20),gzls numeric(16),cr_na varchar(20),cr_dae datetime)
Create table #work2(m_key int IDENTITY (1, 1) NOT NULL,order_jl varchar(250),strGysx Varchar(10),che int,strgybh varchar(50),strcpgyh varchar(50), strgymq Varchar(100), gs numeric(16,4),jjgs numeric(16,4),rs numeric(16,2),strbb varchar(10),gzgroup varchar(20),gzls numeric(16),cr_na varchar(20),cr_dae datetime)
Insert Into #work(order_jl,strgysx,che,strgybh,strcpgyh,gs,jjgs,rs,cr_na,cr_dae)
Select Case When Len(strGysx)=1 Then '0' Else '' End+strGysx+strgybh,Case When Len(strGysx)=1 Then '0' Else '' End+strGysx,
@xx as che,strgybh,strCpgyh,numGs,numJjgs,intJJrs,
crename,credate from CraftWorkProduct where strcpgyh=@wlph and bdel <>1
while @xx <@cc
begin
set @xx=@xx+1
insert into #work(order_jl,strGysx,che,strgybh,strcpgyh,gs,jjgs,rs,cr_na,cr_dae)
Select #work.order_jl+Case When Len(a.strGysx)=1 Then '0' Else '' End+a.strGysx+a.strgybh as order_jl,Case When Len(a.strGysx)=1 Then '0' Else '' End+a.strGysx,
@xx as che,a.strgybh,a.strCpgyh,a.numGs,a.numJjgs,a.intJJrs,
a.crename,a.credate from #work
Inner join CraftWorkProduct as a on (a.strCpgyh=#work.strGybh)
Where #work.che=@xx-1 and a.bdel <>1
continue
end
Insert into #work1(order_jl,strGysx,che,strgybh,strcpgyh,strgymq,gs,jjgs,rs,gzgroup,gzls,cr_na,cr_dae)
Select a.order_jl,a.strGysx,a.che,a.strgybh,a.strcpgyh,b.strgymq,b.numgs,b.numjjgs,a.rs,c.strname,c.btype,a.cr_na,a.cr_dae From #work as a
Left Join CraftWork as b On a.strGybh=b.strGybh and b.bdel=0
Left Join CraftWorkGroup c On b.intGzz=c.id and c.bdel=0
Order by order_jl
Drop Table #work
Select @xx=Max(m_key),@che=Max(che) from #work1
set @xx1=@xx
set @gs=0
set @jjgs=0
set @rs=0
While @che>1
Begin
While @xx1>0
Begin
While Exists(Select * From #work1 Where m_key=@xx1 and che=@che)
Begin
If @rsjt=0
Select @gs=@gs+gs,@jjgs=@jjgs+jjgs,@rs=@rs+rs From #work1 Where m_key=@xx1 and che=@che
Else
Select @gs=@gs+gs,@jjgs=@jjgs+jjgs,@rs=@rs+rs From #work1 Where m_key=@xx1 and gzls>0 and che=@che
Set @xx1=@xx1-1
End
If (@gs>0)
Begin
Update #work1 Set gs=@gs,jjgs=@jjgs,rs=@rs Where m_key=@xx1
Set @gs=0
Set @jjgs=0
Set @rs=0
End
Set @xx1=@xx1-1
End
Set @che=@che-1
End
If @rsjt=0
Insert into #work2(order_jl,strGysx,che,strgybh,strcpgyh,strgymq,gs,jjgs,rs,gzgroup,gzls,cr_na,cr_dae)
Select order_jl,strGysx,che,strgybh,strcpgyh,strgymq,gs,jjgs,rs,gzgroup,gzls,cr_na,cr_dae From #work1 Order by order_jl
Else
Insert into #work2(order_jl,strGysx,che,strgybh,strcpgyh,strgymq,gs,jjgs,rs,gzgroup,gzls,cr_na,cr_dae)
Select order_jl,strGysx,che,strgybh,strcpgyh,strgymq,gs,jjgs,rs,gzgroup,gzls,cr_na,cr_dae From #work1 Where gzls <>1 or gzls is null Order by order_jl
set nocount off
Select * From #work2
GO