declare @details nvarchar(50) set @details='10.2.0.1 DEV_10 zhang_san'select left(@details,charindex(' ',@details)-1) as ip, substring(@details,charindex(' ',@details)+1,charindex(' ',substring(@details,charindex(' ',@details)+1,len(@details)))-1) as department, right(@details,charindex(' ',REVERSE(@details))-1) as user
我想把上面的写成函数,传入的参数为Details 返回值为 IP Department User 是不是在结尾Return @IP,@Department,@User就行了?还有其他注意事项吗?
我按照网上的提示写了一个标值函数: Create Function [Split](@ObjectID nvarchar(150)) Returns @Split table (IP nvarchar(30), Department nvarchar(50), UserName nvarchar(50)) As Begin Declare @details nvarchar(150)set @details = (select Value + ' ' from [VirtualManagerDB_1].[dbo].[tbl_BTBS_CustomPropertyValue] where ObjectID = @ObjectID for XML Path(''))insert into @Split select LEFT (@details, CHARINDEX (' ',@details)-1) as IP ,substring(@details,charindex(' ',@details)+1,charindex(' ',substring(@details,charindex(' ',@details)+1,len(@details)))-1) as Department ,RIGHT (@details, CHARINDEX(' ',REVERSE(@details))-1) as UserName from [VirtualManagerDB_1].[dbo].[tbl_BTBS_CustomPropertyValue] where OBJECTID = @ObjectIDReturn End 在左侧的导航中也能看到已经创建好的函数, 但是为什么执行的时候却显示没有这个函数呢?最后我把它改成了3个标量函数才可以运行的,求解
set @details='10.2.0.1 DEV_10 zhang_san'select left(@details,charindex(' ',@details)-1) as ip,
substring(@details,charindex(' ',@details)+1,charindex(' ',substring(@details,charindex(' ',@details)+1,len(@details)))-1) as department,
right(@details,charindex(' ',REVERSE(@details))-1) as user
我想把上面的写成函数,传入的参数为Details 返回值为 IP Department User
是不是在结尾Return @IP,@Department,@User就行了?还有其他注意事项吗?
表值函数才可以返回一组值还有我看你上面的例子zhang san这里貌似不是下划线,是个空格吧,如果是空格的话
我那条user的语句就有问题了,你可以参照截取第二个再截取一下
Create Function [Split](@ObjectID nvarchar(150))
Returns @Split table (IP nvarchar(30), Department nvarchar(50), UserName nvarchar(50))
As
Begin
Declare @details nvarchar(150)set @details = (select Value + ' ' from [VirtualManagerDB_1].[dbo].[tbl_BTBS_CustomPropertyValue] where ObjectID = @ObjectID for XML Path(''))insert into @Split
select LEFT (@details, CHARINDEX (' ',@details)-1) as IP
,substring(@details,charindex(' ',@details)+1,charindex(' ',substring(@details,charindex(' ',@details)+1,len(@details)))-1) as Department
,RIGHT (@details, CHARINDEX(' ',REVERSE(@details))-1) as UserName
from [VirtualManagerDB_1].[dbo].[tbl_BTBS_CustomPropertyValue] where OBJECTID = @ObjectIDReturn
End
在左侧的导航中也能看到已经创建好的函数,
但是为什么执行的时候却显示没有这个函数呢?最后我把它改成了3个标量函数才可以运行的,求解
当做一个表来引用,
另,2005以后可以用cross apply来连接表值函数