传递到 SUBSTRING 函数的长度参数无效（搞不清思路）

[HIpity]

ALTER FUNCTION [dbo].[Fn_ProdAttr_IsUse]
(
 @AttrRelIDList VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
declare @returnValue int
SET @returnValue=0

while(charindex(',',@AttrRelIDList)>0)  
begin
   IF(EXISTS(SELECT * FROM tb_base_prod WHERE Group_ID=substring(@AttrRelIDList,1,charindex(',',@AttrRelIDList)-1)))
    SET @returnValue=1
    set @AttrRelIDList=stuff(@AttrRelIDList,1,charindex(',',@AttrRelIDList),'')
   IF(EXISTS(SELECT * FROM tb_Base_Prod_Attr_Rel WHERE Father_ID=substring(@AttrRelIDList,1,charindex(',',@AttrRelIDList)-1)))
    SET @returnValue=1
    set @AttrRelIDList=stuff(@AttrRelIDList,1,charindex(',',@AttrRelIDList),'')
end  
IF(EXISTS(SELECT * FROM tb_base_prod WHERE Group_ID=@AttrRelIDList))
  SET @returnValue=1
IF(EXISTS(SELECT * FROM tb_Base_Prod_Attr_Rel WHERE Father_ID=@AttrRelIDList))
  SET @returnValue=1  
  
return @returnValue
END

测试
select dbo.Fn_ProdAttr_IsUse('333,333')报错
消息 536，级别 16，状态 5，第 1 行
传递到 SUBSTRING 函数的长度参数无效。
按理说是substring里传入 负值索引，我感觉这个函数是对的

[快溜]

传值需要加个逗号吧，你判断的逻辑问题

[AcHerat]

charindex(',',@AttrRelIDList)-1

如果,号在开始位置，这个截取怎么说。

[AcHerat]

那个循环体里的判断是不是有问题。

意思是如果两个表有其中一个存在满足判断的条件就去对你那个变量做处理么？

while ...
begin
if exists () or exists ()
begin
set ...
set ...
end
end

这样子。

[中国风]

ALTER FUNCTION [dbo].[Fn_ProdAttr_IsUse]
(
 @AttrRelIDList VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
declare @returnValue int
SET @returnValue=0

while(charindex(',',@AttrRelIDList)>0)  
begin
   IF(EXISTS(SELECT * FROM tb_base_prod WHERE Group_ID=substring(@AttrRelIDList,1,charindex(',',@AttrRelIDList)-1)))
	
		SET @returnValue=1

   else IF (EXISTS(SELECT * FROM tb_Base_Prod_Attr_Rel WHERE Father_ID=substring(@AttrRelIDList,1,charindex(',',@AttrRelIDList)-1)))
	
		SET @returnValue=1
set @AttrRelIDList=stuff(@AttrRelIDList,1,charindex(',',@AttrRelIDList),'')
end  
IF(EXISTS(SELECT * FROM tb_base_prod WHERE Group_ID=@AttrRelIDList))
  SET @returnValue=1
IF(EXISTS(SELECT * FROM tb_Base_Prod_Attr_Rel WHERE Father_ID=@AttrRelIDList))
  SET @returnValue=1  
  
return @returnValue
END
GO
select dbo.Fn_ProdAttr_IsUse('333,333')

[中国风]

按以上方法測測

[中国风]

改改，少改了一個位置

ALTER FUNCTION [dbo].[Fn_ProdAttr_IsUse]
(
 @AttrRelIDList VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
declare @returnValue int
SET @returnValue=0

while(charindex(',',@AttrRelIDList)>0)  
begin
   IF(EXISTS(SELECT * FROM tb_base_prod WHERE Group_ID=substring(@AttrRelIDList,1,charindex(',',@AttrRelIDList)-1)))
	
		SET @returnValue=1

   else IF (EXISTS(SELECT * FROM tb_Base_Prod_Attr_Rel WHERE Father_ID=substring(@AttrRelIDList,1,charindex(',',@AttrRelIDList)-1)))
	
		SET @returnValue=1
set @AttrRelIDList=stuff(@AttrRelIDList,1,charindex(',',@AttrRelIDList),'')
end  
IF(EXISTS(SELECT * FROM tb_base_prod WHERE Group_ID=@AttrRelIDList))
  SET @returnValue=1
ELSE IF(EXISTS(SELECT * FROM tb_Base_Prod_Attr_Rel WHERE Father_ID=@AttrRelIDList))
  SET @returnValue=1  
  
return @returnValue
END
GO
select dbo.Fn_ProdAttr_IsUse('333,333')

[-晴天]

try:
select dbo.Fn_ProdAttr_IsUse('333,333,')

[中国风]

樓主測測看優化后的腳本

ALTER FUNCTION [dbo].[Fn_ProdAttr_IsUse]
(
 @AttrRelIDList VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
	declare @returnValue int
	SET @returnValue=0

	if EXISTS(SELECT * FROM tb_base_prod WHERE ','+@AttrRelIDList+',' LIKE  '%,'+RTRIM(Group_ID)+',%')
	OR EXISTS(SELECT * FROM tb_Base_Prod_Attr_Rel WHERE ','+@AttrRelIDList+',' LIKE  '%,'+RTRIM(Father_ID)+',%')
		SET @returnValue=1  
	return @returnValue
END
GO
select dbo.Fn_ProdAttr_IsUse('333,333')

[--小F--]

IF(EXISTS(SELECT * FROM tb_base_prod WHERE Group_ID=substring(@AttrRelIDList,1,charindex(',',@AttrRelIDList)-1)))
SET @returnValue=1
set @AttrRelIDList=stuff(@AttrRelIDList,1,charindex(',',@AttrRelIDList),'')
IF(EXISTS(SELECT * FROM tb_Base_Prod_Attr_Rel WHERE Father_ID=substring(@AttrRelIDList,1,charindex(',',@AttrRelIDList)-1)))
SET @returnValue=1
set @AttrRelIDList=stuff(@AttrRelIDList,1,charindex(',',@AttrRelIDList),'')


这两段完全可以合并。

[--小F--]

而且在你的存储过程中判断,用的是 ','+@col+','格式的

而你传参直接是'333,333'

估计这个也有问题