向 substring 函数传递了无效的 length 参数

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