11,850
社区成员
发帖
与我相关
我的任务
分享
USE [TzglDb]
GO
/****** Object: UserDefinedFunction [dbo].[get_htcq] Script Date: 08/28/2017 13:24:04 ******/
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[get_htcq]
(
)
RETURNS
@htcq TABLE
(
-- Add the column definitions for the TABLE variable here
htno varchar(20),
lp varchar(50),
lz varchar(50),
roomno varchar(50),
cqno varchar(100),
khno varchar(20),
zqstart datetime,
zqend datetime,
flg int,
lzh varchar(50),
ocode varchar(5)
)
AS
BEGIN
DECLARE @count int
declare @i int
declare @lp varchar(200)
declare @lz varchar(200)
declare @roomno varchar(200)
declare @cqno varchar(500)
declare @htno varchar(20)
declare @sql varchar(100)
set @i=1
select top 1 @htno=htno from ht_mst
select @count=COUNT(*) from HT_MST
while @i<=@count
begin
--select top 1 @lp=lp,@lz=lz,@roomno=roomno,@cqno=cqno,@htno=htno from HT_MST where htno NOT IN (select tops @i htno from HT_MST order by htno)order by htno
select @lp=lp,@lz=lz,@roomno=roomno,@cqno=cqno,@htno=htno from HT_MST where htno=@htno
insert into @htcq(lp,lz,roomno,cqno,htno,khno,zqstart,zqend,flg,lzh,ocode)select lp,lz,roomno,cqno,htno,khno,zqstart,zqend,flg,lp+roomno,ocode from (select * from get_cqinf(@lp,@lz,@roomno,@cqno)) as cc left join (select HT_MST.htno,HT_KH.khno,zqstart,zqend,HT_MST.flg,HT_MST.ocode from HT_MST,HT_KH where HT_MST.htno=HT_KH.htno) as qq on qq.htno=@htno
select @htno=htno from HT_MST where htno in (select top 1 htno from HT_MST where htno>@htno ) order by htno
set @i= @i+1
end
return
END
USE [TzglDb]
GO
/****** Object: UserDefinedFunction [dbo].[get_cqinf] Script Date: 08/28/2017 14:33:17 ******/
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[get_cqinf]
(
-- Add the parameters for the function here
@lp varchar(100),
@lz varchar(100),
@roomno varchar(100),
@cqno varchar(500)
)
RETURNS
@cqinf TABLE
(
-- Add the column definitions for the TABLE variable here
lp varchar(20),
lz varchar(20),
roomno varchar(20),
cqno varchar(50)
)
AS
BEGIN
DECLARE @a int, @b int,@c int,@d int,@i int
set @lp = @lp+','
set @lz =@lz+','
set @roomno=@roomno+','
set @cqno=@cqno+','
set @i=1
while len(@lp)>1
begin
set @a=charindex(',',@lp)
set @b=charindex(',',@lz)
set @c=charindex(',',@roomno)
set @d=charindex(',',@cqno)
insert into @cqinf(lp,lz,roomno,cqno) values(left(@lp, @a -1),left(@lz, @b -1),left(@roomno, @c -1),left(@cqno, @d -1))
set @lp=right(@lp,len(@lp)-@a)
set @lz=right(@lz,len(@lz)-@b)
set @roomno=right(@roomno,len(@roomno)-@c)
set @cqno=right(@cqno,len(@cqno)-@d)
end
RETURN
END
和这个函数没多大关系吧?我来看看[/quote]
不对,bushizhege
还别说,好像真的是这个函数有问题,我查询出来报错:
必须声明变量 '@lp'。[/quote]
不对,不是这个报错。请忽视我的三楼
USE [TzglDb]
GO
/****** Object: UserDefinedFunction [dbo].[get_cqinf] Script Date: 08/28/2017 14:33:17 ******/
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[get_cqinf]
(
-- Add the parameters for the function here
@lp varchar(100),
@lz varchar(100),
@roomno varchar(100),
@cqno varchar(500)
)
RETURNS
@cqinf TABLE
(
-- Add the column definitions for the TABLE variable here
lp varchar(20),
lz varchar(20),
roomno varchar(20),
cqno varchar(50)
)
AS
BEGIN
DECLARE @a int, @b int,@c int,@d int,@i int
set @lp = @lp+','
set @lz =@lz+','
set @roomno=@roomno+','
set @cqno=@cqno+','
set @i=1
while len(@lp)>1
begin
set @a=charindex(',',@lp)
set @b=charindex(',',@lz)
set @c=charindex(',',@roomno)
set @d=charindex(',',@cqno)
insert into @cqinf(lp,lz,roomno,cqno) values(left(@lp, @a -1),left(@lz, @b -1),left(@roomno, @c -1),left(@cqno, @d -1))
set @lp=right(@lp,len(@lp)-@a)
set @lz=right(@lz,len(@lz)-@b)
set @roomno=right(@roomno,len(@roomno)-@c)
set @cqno=right(@cqno,len(@cqno)-@d)
end
RETURN
END
和这个函数没多大关系吧?我来看看