Sqlserver中存储过程和游标的一些使用例子

/*带输入输出参数存储过程*/
ALTER PROCEDURE pro_test2
	@userID INT,
	@maxUserID INT OUTPUT,
	@countUser INT OUTPUT
AS
    BEGIN  
        SELECT * FROM dbo.SY_ADMIN WHERE UserID=@userID  --10075
		SELECT @maxUserID=10089
		SET @countUser=20000
    END;  
GO  

/*测试*/
DECLARE @maxUserID INT,@countUser INT
EXECUTE pro_test2 10075,@maxUserID OUTPUT,@countUser OUTPUT
SELECT a=@countUser, b=@maxUserID



/*带输入输出参数 0登录成功 1密码错误 2用户名错误*/
ALTER PROC proc_test3
    @UserName VARCHAR(50),
    @UserPwd VARCHAR(50),
	@Result INT OUT
AS
    BEGIN  
        IF @UserName = 'admin'
            BEGIN  
                IF @UserPwd = '111'
                    SET @Result = 0 
                ELSE
                    SET @Result = 1 
            END;   
        ELSE
            SET @Result = 2 
    END  

/*测试*/
DECLARE @Result INT
EXECUTE proc_test3 'hystu1', '111',@Result OUT
SELECT a=  @Result



/*游标使用例子*/
--声明2个变量
DECLARE @D_Id NVARCHAR(MAX);   
DECLARE @D_Name NVARCHAR(MAX); 
DECLARE @D_Password NVARCHAR(MAX);   
--声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同  
DECLARE mycursor CURSOR
FOR
    SELECT  D_Id ,
            D_Name,
			D_Password
    FROM    dbo.TestTable;    
--打开游标  
OPEN mycursor;