SQL存储过程登陆实例

来源：互联网发布：休闲运动套装淘宝店编辑：程序博客网时间：2024/05/18 01:54

--CREATE DATABASE UserManage--use UserManageCREATE TABLE UserInfo(    UserID int primary key identity(1,1),    UserName nvarchar(20) not null,    UserPassword nvarchar(20) not null,    IsAllow BIT NOT NULL)INSERT INTO UserInfo VALUES('ROOT','123','true')INSERT INTO UserInfo VALUES('admin','admin','true')INSERT INTO UserInfo VALUES('123','123','false')INSERT INTO UserInfo VALUES('234','123','false')INSERT INTO UserInfo VALUES('345','123','false')SELECT * FROM UserInfo

--USE [UserManage]--GO--DECLARE   @return_value int,--      @result nvarchar(50)--EXEC  @return_value = [dbo].[UserLogin]--      @name = root,--      @pwd = 123,--      @result = @result OUTPUT--SELECT    @result as N'@result'--SELECT    'Return Value' = @return_value--GO

--CREATE PROC UserLogin ALTER PROC UserLogin @name nvarchar(20), --传入参数@pwd nvarchar(20),@result nvarchar(50) out--返回值ASDECLARE @UserName nvarchar(20),--变量声明@UserPwd nvarchar(20),@Count int,@IsAllow BITBEGIN--获取用户名SELECT @Count= COUNT(1) FROM UserInfo WHERE UserName = @name    IF(@Count = 0 OR @Count IS NULL)    BEGIN        SET @result = '用户名不存在！';    END    ELSE IF(@Count = 1)    BEGIN        SELECT @UserPwd = UserPassword,@IsAllow = IsAllow FROM UserInfo WHERE UserName = @name        IF( @UserPwd = @pwd)        BEGIN            IF(@IsAllow = 1)            BEGIN                SET @result = '登陆成功！';            END            ELSE            BEGIN                SET @result = '没有权限登陆系统！';            END        END        ELSE        BEGIN            SET @result = '密码错误！';        END    ENDENDGO

0 0