sql语句 自定义函数调用 使用SELECT调用
本帖最后由 qilinno 于 2013-10-17 21:40:45 编辑

CREATE FUNCTION COMMRANK(@XH NVARCHAR(20))
RETURNS TABLE
AS
BEGIN
    -----------------------------------------------游标----------------------------------------------------
    DECLARE cursor_rank CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR
    SELECT COUNT(*) AS 'CC' FROM [SuInMaSy].[dbo].[CommDetail] GROUP BY [CommNo] ORDER BY 'CC' DESC

    OPEN cursor_rank
    DECLARE @c NVARCHAR(20)
    DECLARE @i INT
    SET @i = 1
-----------------------------------------------使用游标---------------------------------------------
    WHILE @@FETCH_STATUS=0
        BEGIN
        FETCH NEXT FROM cursor_rank INTO @c
        IF @c <> @XH
            BEGIN
            SET @i+=1
            END
        END
    CLOSE cursor_rank
    DEALLOCATE cursor_rank

    RETURN (SELECT @i)
END

GO

SELECT * FROM COMMRANK('tod')
GO

请问这样调用有什么错误？
谢谢！ sql语句 自定义函数调用? 使用SELECT调用
[解决办法]
呵呵，改一下：

---------------------------------------------使用函数--------------------------------------------
CREATE FUNCTION COMMRANK(@XH NVARCHAR(20))
RETURNS INT
AS
BEGIN
-----------------------------------------------游标----------------------------------------------------
DECLARE cursor_rank CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR
SELECT COUNT(*) AS 'CC' FROM [SuInMaSy].[dbo].[CommDetail] GROUP BY [CommNo] ORDER BY 'CC' DESC

OPEN cursor_rank

DECLARE @c NVARCHAR(20)
DECLARE @i INT
SET @i = 1

        FETCH NEXT FROM cursor_rank INTO @c

        WHILE @@FETCH_STATUS=0
BEGIN
IF @c <> @XH
SET @i+=1
ELSE
BREAK

                FETCH NEXT FROM cursor_rank INTO @c
END
CLOSE cursor_rank
DEALLOCATE cursor_rank

RETURN (@i)
END