如何检查用户名是否存在

How to return 1 if username exists and 0 in other case? Here is what I've tried.

如果用户名存在，如何返回1;如果用户名存在，如何返回0 ?这是我试过的。

IF EXISTS (SELECT * FROM [User] WHERE UserName = @UserName and EmailID=@EmailID) 
    begin
      return 1;
    end
     else
      begin
    declare @CreatedOn datetime
    select @CreatedOn = getdate()
    insert into [User](UserName,Password,EmailID,ContactNo,CreatedOn) values(@UserName,@Password,@EmailID,@ContactNo,@CreatedOn)

6 个解决方案

#1

Maybe you could try this query:

也许你可以试试这个问题:

Select count(*) from [user] where  UserName = @UserName and EmailID=@EmailID

and if user name exists count(*) will be larger then 0

如果用户名存在，则count(*)将大于0。

#2

CREATE PROCEDURE CheckUserExists
AS
    @UserName NVARCHAR(MAX),
    @Email NVARCHAR(MAX)
BEGIN    
    IF EXISTS (SELECT 1 FROM [User] WHERE UserName = @UserName OR EmailID=@EmailID) 
    BEGIN
        RETURN 1
    END
    ELSE BEGIN
        RETURN 0    
    END
END

#3

I see a bigger problem here. you stated you want to know if the user exists or not. but the query shows a side affect, that if the user doesn't exist you will create the user in the same call. these should be separate sqlcommands. one for the query/reading and another for the command/insert.

我看到了一个更大的问题。您表示希望知道用户是否存在。但是查询显示了一个副作用，如果用户不存在，您将在同一个调用中创建用户。这些应该是单独的sqlcommand。一个用于查询/读取，另一个用于命令/插入。

to determine if the user exists a simple select 1 from [users] where [username] = @user should suffice. if any (1?) rows are returned the user exists, otherwise the user does not exist.

要确定用户是否存在，只需从[users]中选择1，其中[username] = @user就足够了。如果返回任何(1?)行，则用户存在，否则用户不存在。

you can then issue the command to create the user

然后，您可以发出命令来创建用户

insert into [User](UserName,Password,EmailID,ContactNo,CreatedOn) values(@UserName,@Password,@EmailID,@ContactNo,getdate())

#4

Try this:

试试这个:

IF EXISTS (SELECT 1 FROM [User] WHERE UserName = @UserName and EmailID=@EmailID) 
    begin
      return 1;
    end
     else
       return 0;

#5

/* Execute the SP As*/
DECLARE @return_value int
EXEC    @return_value = [dbo].[CheckUserExists]
Select @return_value

/*STORED PROCEDURE*/
CREATE PROCEDURE CheckUserExists
AS
    @UserName NVARCHAR(MAX),
    @Password NVARCHAR(MAX)
    @EmailID NVARCHAR(MAX),
    @ContactNo NVARCHAR(MAX)
BEGIN

IF EXISTS (SELECT 1 FROM [User] WHERE UserName = @UserName and EmailID=@EmailID) 
    BEGIN
      RETURN 1;
    END
     ELSE
      BEGIN
        DECLARE @CreatedOn DATETIME
        SELECT @CreatedOn = GETDATE()
        INSERT INTO [User](UserName,Password,EmailID,ContactNo,CreatedOn) 
        VALUES(@UserName,@Password,@EmailID,@ContactNo,@CreatedOn)
        RETURN 0;
END

#6

CREATE PROCEDURE CheckUserExists AS @UserName NVARCHAR(MAX), @Email NVARCHAR(MAX) BEGIN
declare @usercount int

创建程序CheckUserExists为@UserName NVARCHAR(MAX)， @Email NVARCHAR(MAX)开始声明@usercount int。

Select @usercount = count(UserName) from [user] where  UserName = @UserName and EmailID=@EmailID

IF (@usercount = 1) 
BEGIN
    RETURN 1
END
ELSE BEGIN
    RETURN 0    
END

END

结束

#1