-- =============================================
-- Create function Get_father
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'Get_father')
DROP FUNCTION Get_father
GOCREATE FUNCTION Get_father (@FAccountID int)
RETURNS varchar(2000)
ASBEGIN
DECLARE @FNumber varchar(2000),@FParentID int,@TMPSTR varchar(2000),@COUNT int
,@Parent_FNumber varchar(2000)
DECLARE FA_cursor CURSOR FOR
select FNumber,FParentID
from t_account
where FAccountID=@FAccountID
DECLARE FP_cursor CURSOR FOR
select FNumber,FParentID
from t_account
where FAccountID=@FParentIDSET @COUNT=0
SET @TMPSTR=''
WHILE (@COUNT<=10)
BEGIN
OPEN FA_cursor
FETCH NEXT FROM FA_cursor INTO @FNumber,@FParentID
IF @@FETCH_STATUS = 0
BEGIN
IF @FParentID=0
BEGIN
SET @TMPSTR=STR(LEN(@FNumber))+@TMPSTR
WHILE (10-@COUNT)>0
BEGIN
SET @TMPSTR=@TMPSTR+',0'
SET @COUNT=@COUNT+1
END
END
ELSE
BEGIN
OPEN FP_cursor
FETCH NEXT FROM FP_cursor INTO @Parent_FNumber,@FParentID
IF @@FETCH_STATUS = 0
SET @TMPSTR=','+STR(LEN(@FNumber)-LEN(@Parent_FNumber))+@TMPSTR
CLOSE FP_cursor4
END
END
SET @COUNT=@COUNT+1
END
CLOSE FA_cursor
END
DEALLOCATE FA_cursor
DEALLOCATE FP_cursor
RETURN @TMPSTR
END
GO
---------------------------------------------------------
提示出错
服务器: 消息 156,级别 15,状态 1,过程 Get_father,行 48
在关键字 'DEALLOCATE' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,过程 Get_father,行 51
在关键字 'END' 附近有语法错误。
-- Create function Get_father
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'Get_father')
DROP FUNCTION Get_father
GOCREATE FUNCTION Get_father (@FAccountID int)
RETURNS varchar(2000)
ASBEGIN
DECLARE @FNumber varchar(2000),@FParentID int,@TMPSTR varchar(2000),@COUNT int
,@Parent_FNumber varchar(2000)
DECLARE FA_cursor CURSOR FOR
select FNumber,FParentID
from t_account
where FAccountID=@FAccountID
DECLARE FP_cursor CURSOR FOR
select FNumber,FParentID
from t_account
where FAccountID=@FParentIDSET @COUNT=0
SET @TMPSTR=''
WHILE (@COUNT<=10)
BEGIN
OPEN FA_cursor
FETCH NEXT FROM FA_cursor INTO @FNumber,@FParentID
IF @@FETCH_STATUS = 0
BEGIN
IF @FParentID=0
BEGIN
SET @TMPSTR=STR(LEN(@FNumber))+@TMPSTR
WHILE (10-@COUNT)>0
BEGIN
SET @TMPSTR=@TMPSTR+',0'
SET @COUNT=@COUNT+1
END
END
ELSE
BEGIN
OPEN FP_cursor
FETCH NEXT FROM FP_cursor INTO @Parent_FNumber,@FParentID
IF @@FETCH_STATUS = 0
SET @TMPSTR=','+STR(LEN(@FNumber)-LEN(@Parent_FNumber))+@TMPSTR
CLOSE FP_cursor4
END
END
SET @COUNT=@COUNT+1
END
CLOSE FA_cursor
END
DEALLOCATE FA_cursor
DEALLOCATE FP_cursor
RETURN @TMPSTR
END
GO
---------------------------------------------------------
提示出错
服务器: 消息 156,级别 15,状态 1,过程 Get_father,行 48
在关键字 'DEALLOCATE' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,过程 Get_father,行 51
在关键字 'END' 附近有语法错误。
-- Create function Get_father
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'Get_father')
DROP FUNCTION Get_father
GOCREATE FUNCTION Get_father (@FAccountID int)
RETURNS varchar(2000)
ASBEGIN
DECLARE @FNumber varchar(2000),@FParentID int,@TMPSTR varchar(2000),@COUNT int
,@Parent_FNumber varchar(2000)
DECLARE FA_cursor CURSOR FOR
select FNumber,FParentID
from t_account
where FAccountID=@FAccountID
DECLARE FP_cursor CURSOR FOR
select FNumber,FParentID
from t_account
where FAccountID=@FParentIDSET @COUNT=0
SET @TMPSTR=''
OPEN FA_cursor
OPEN FP_cursor
WHILE (@COUNT<=10)
BEGIN
FETCH NEXT FROM FA_cursor INTO @FNumber,@FParentID
IF @@FETCH_STATUS = 0
BEGIN
IF @FParentID=0
BEGIN
SET @TMPSTR=STR(LEN(@FNumber))+@TMPSTR
WHILE (10-@COUNT)>0
BEGIN
SET @TMPSTR=@TMPSTR+',0'
SET @COUNT=@COUNT+1
END
END
ELSE
BEGIN
FETCH NEXT FROM FP_cursor INTO @Parent_FNumber,@FParentID
IF @@FETCH_STATUS = 0
SET @TMPSTR=','+STR(LEN(@FNumber)-LEN(@Parent_FNumber))+@TMPSTR
-- CLOSE FP_cursor
END
END
SET @COUNT=@COUNT+1
--END
END
CLOSE FA_cursor
CLOSE FP_cursor
DEALLOCATE FA_cursor
DEALLOCATE FP_cursor
RETURN @TMPSTR
END
GO
END
CLOSE FA_cursorDEALLOCATE FA_cursor
DEALLOCATE FP_cursor
RETURN @TMPSTR
END
在函数里可不可以用
PRINT 'ok'
这样的语句
select FNumber,FParentID
from t_account
where FAccountID=@FAccountID
SET @FAccountID=1000
OPEN FA_cursor
...........
CLOSE FA_cursor
SET @FAccountID=1002
OPEN FA_cursor
...........
CLOSE FA_cursor
-----------------------------
这们的写法行不行?