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
---------------------------------------------使用函数--------------------------------------------
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
