IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[SP_Check_QQ]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[SP_Check_QQ]
GO
CREATE PROCEDURE dbo.SP_Check_QQ
--做为检验QQ号是否全为数字,不用变量
AS
--主要是练习用游标
--申明游标
DECLARE @Q varchar(10)
DECLARE @id integer
DECLARE @s varchar(8000)
/*
*定义游标
*/
DECLARE cur_II CURSOR FOR
SELECT II_id,QQ FROM II
/*
*打开游标
*/
OPEN cur_ii
/*
*提取记录
*/
FETCH next FROM cur_ii into @id,@Q
SET @s=''
/*
*循环并提取记录
*/
WHILE ( @@FETCH_STATUS = 0)
BEGIN
IF dbo.is_all_number(@Q)=0
SET @s=@s+','+cast(@id AS varchar(10))
FETCH next FROM cur_ii
into @id,@Q
END
/*
*关闭游标
*/
CLOSE cur_ii
/*
*删除游标引用
*/
DEALLOCATE cur_ii
SET @s=right(@s,len(@s)-1)
SELECT @s
/*
exec SP_Check_QQ
*/
go