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.带返回值的存储过程