CREATE FUNCTION [dbo].[FN_TopGetOrgByUserName] ( @UserName NVARCHAR(128) ) RETURNS @showOrg TABLE(id NVARCHAR(36)) AS BEGIN DECLARE @baseOrg TABLE(id NVARCHAR(36), fullpath NVARCHAR(200), shortName NVARCHAR(200), parentid NVARCHAR(36), [level] INT,index_baseOrg INT) INSERT INTO @baseOrg SELECT id,fullpath,shortName,parentid,[level],ROW_NUMBER()OVER(ORDER BY id) FROM SysOrganization WHERE id IN ( SELECT OrgID FROM SysPosition ,SysUserPosition,SysUser WHERE SysPosition.ID=SysPosition AND SysUser.ID=SysUser_ID AND UserName=@UserName AND basepositionid='' ) AND IsEnable=1 AND (IsDel=0 OR IsDel IS NULL) DECLARE @i INT DECLARE @j INT SET @i=1 SELECT @j=COUNT(*)FROM @baseOrg WHERE [level]=1 IF @j=1 BEGIN INSERT INTO @showOrg SELECT id FROM SysOrganization WHERE IsEnable=1 AND( IsDel=0 OR isdel IS NULL ) END ELSE BEGIN SELECT @j=COUNT(*)FROM @baseOrg WHILE @i<=@j BEGIN DECLARE @currentID NVARCHAR(36) SELECT @currentID=ID FROM @baseOrg WHERE index_baseOrg=@i DECLARE @levelChild INT DECLARE @t_level TABLE(id VARCHAR(MAX) , [level] INT) SET @levelChild = 1 INSERT @t_level SELECT @currentID, @levelChild WHILE @@ROWCOUNT > 0 BEGIN SET @levelChild = @levelChild + 1 INSERT INTO @t_level SELECT a.ID , @levelChild FROM SysOrganization a , @t_Level b WHERE a.ParentID = b.id AND b.[level] = @levelChild - 1 END INSERT INTO @showOrg SELECT id FROM @t_level WHERE id NOT IN (SELECT id FROM @showOrg) SET @i=@i+1 END END RETURN END GO
Split 表函数将一个字符串按指定分隔符进行分割,返回一个表。 charindex:在一段字符中搜索字符或者字符串
create function split( @string varchar(255),--待分割字符串 @separator varchar(255)--分割符 )returns @array table(item varchar(255)) as begin declare @begin int,@end int,@item varchar(255) set @begin = 1 set @end=charindex(@separator,@string,@begin) while(@end<>0) begin set @item = substring(@string,@begin,@end-@begin) insert into @array(item) values(@item) set @begin = @end+1 set @end=charindex(@separator,@string,@begin) end set @item = substring(@string,@begin,len(@string)+1-@begin) if (len(@item)>0) insert into @array(item) values(substring(@string,@begin,len(@string)+1-@begin)) return end
标量值函数:返回一个值
--用户自定义函数 CREATE FUNCTION dbo.core_getfilesize ( @size int ) RETURNS varchar(50) AS BEGIN DECLARE @m FLOAT; DECLARE @s VARCHAR(50); SET @m = ROUND(CAST(@size AS FLOAT) / 1024, 2) IF ( @m > 1024 ) BEGIN SET @m = ROUND(CAST(@size AS FLOAT) / 1048576, 2) SET @s = CONVERT(VARCHAR(18), @m) + 'M'; END ELSE BEGIN SET @s = CONVERT(VARCHAR(18), @m) + 'K'; END RETURN @s; END --调用 SELECT dbo.core_getfilesize(1048576000)
表值函数:返回一张表
CREATE FUNCTION getuses ( @id int ) RETURNS TABLE AS RETURN ( SELECT * FROM biaoming(nolock) r WHERE r.id=@id )
存储过程:
1.不带返回值的存储过程
2.带返回值的存储过程