我写了下面的定义函数,主要是分割字符串的CREATE FUNCTION f_splitSTR(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO 下面是调用的sql文
SELECT AFW_BPInfo.cusName,AFW_ContractInfo.orgCusId,AFW_WarrantyInfo.warrantyName
FROM AFW_ContractRelationship,AFW_ContractInfo,AFW_BPInfo,AFW_WarrantyInfo
WHERE AFW_ContractRelationship.status != 'D'
AND AFW_ContractInfo.contractType = 'WAR'
AND AFW_ContractInfo.ContractId = AFW_ContractRelationship.ContractId2
AND AFW_ContractInfo.Status != 'D'
AND AFW_ContractInfo.partnerId = AFW_BPInfo.CustomerId
AND AFW_ContractRelationship.ContractId1 IN (
SELECT AFW_passContract.ContractId
FROM AFW_passContract
WHERE AFW_passContract.contractType = 'CRD'
AND AFW_passContract.Status != 'D'
AND AFW_passContract.customerId = ?
)
and AFW_WarrantyInfo.SeqNo IN (
SELECT col FROM f_splitSTR(
AFW_ContractInfo.description,'-'
)
)
运行的时候红色部分会报错出来,我又试着红色部分直接换成sql文让查询出来做参数,依然会报错,急啊,我在线等
@s varchar(8000), --要分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO 下面是调用的sql文
SELECT AFW_BPInfo.cusName,AFW_ContractInfo.orgCusId,AFW_WarrantyInfo.warrantyName
FROM AFW_ContractRelationship,AFW_ContractInfo,AFW_BPInfo,AFW_WarrantyInfo
WHERE AFW_ContractRelationship.status != 'D'
AND AFW_ContractInfo.contractType = 'WAR'
AND AFW_ContractInfo.ContractId = AFW_ContractRelationship.ContractId2
AND AFW_ContractInfo.Status != 'D'
AND AFW_ContractInfo.partnerId = AFW_BPInfo.CustomerId
AND AFW_ContractRelationship.ContractId1 IN (
SELECT AFW_passContract.ContractId
FROM AFW_passContract
WHERE AFW_passContract.contractType = 'CRD'
AND AFW_passContract.Status != 'D'
AND AFW_passContract.customerId = ?
)
and AFW_WarrantyInfo.SeqNo IN (
SELECT col FROM f_splitSTR(
AFW_ContractInfo.description,'-'
)
)
运行的时候红色部分会报错出来,我又试着红色部分直接换成sql文让查询出来做参数,依然会报错,急啊,我在线等
FROM AFW_ContractRelationship,AFW_ContractInfo,AFW_BPInfo,AFW_WarrantyInfo
WHERE AFW_ContractRelationship.status != 'D'
AND AFW_ContractInfo.contractType = 'WAR'
AND AFW_ContractInfo.ContractId = AFW_ContractRelationship.ContractId2
AND AFW_ContractInfo.Status != 'D'
AND AFW_ContractInfo.partnerId = AFW_BPInfo.CustomerId
AND AFW_ContractRelationship.ContractId1 IN (
SELECT AFW_passContract.ContractId
FROM AFW_passContract
WHERE AFW_passContract.contractType = 'CRD'
AND AFW_passContract.Status != 'D'
AND AFW_passContract.customerId = ?
)
and AFW_WarrantyInfo.SeqNo IN (
SELECT col FROM dbo.f_splitSTR(AFW_ContractInfo.description,'-' ))
--不能直接引用字段,TRY;
f_splitSTR((select description from AFW_ContractInfo) ,'-' )
f_splitSTR((select description from AFW_ContractInfo) ,'-' ) 报(错误f_splitSTR(select description from AFW_ContractInfo,'-' )报select错误
SELECT col FROM f_splitSTR(
AFW_ContractInfo.description,'-'
)
) 改:and CHARINDEX('-'+RTRIM(AFW_WarrantyInfo.SeqNo)+'-','-'+AFW_ContractInfo.description+'-') > 0
试试
联机丛书:
当唤醒调用标量值函数时,至少应使用函数的两部分名称。[database_name.]owner_name.function_name ([argument_expr][,...])如果是表值函数的话,可以只要一段式的形式来引用.也就是不用加上所有者的名称.
联机丛书:
可以使用由一部分组成的名称唤醒调用表值函数。[database_name.][owner_name.]function_name ([argument_expr][,...])
调用函数前面dbo所有者数据库