如何使用存储过程检查表中已退出的用户名 [英] How to check the user name already exits in the table by using stored procedure

问题描述

如果在插入时使用存储过程

检查用户名是否已经退出，如果某人已经使用了用户名，则表示我们必须说用户名是否存在。



这是代码

How to check the user name already exits by using stored procedure
while inserting, if the username already taken by somebody means we have to say user name aleady exists.

Here is the code

create proc sp_user
(
@userid int,
@username varchar(20)
)
as 
begin
insert into tbl_ins(userid, username) values(@userid,@username)
end

请帮助我...



提前致谢...

please help me...

Thanks in advance...

推荐答案

create proc sp_user
(
@userid int,
@username varchar(20)
)
as 
begin
insert into tbl_ins(userid, username) values(@userid,@username)
end

这是你的代码，我对你有一些建议

1）永远不要用SP_的起始字母创建一个程序，看看原因在其中 [ ^ ]

2）try catch的位置在哪里？

要回答你的问题，你有两种方式

1）在表格中的列USername上创建一个唯一约束（Key）tbl_ins

你不需要额外的垃圾代码在这里？

2）（有人说我将在我的Proc中维护我的代码）在proc中有额外的代码在这里

This is your code, i have some suggesstion to you
1) Never create a procedure with The starting letter of SP_ see the reason here[^]
2) where is the try catch?
To answer your problem you have two way
1) Create a unique constraint(Key) on the column USername in the table tbl_ins
You don't need a extra crap code here?
2)(well some one say that i will maintain my code in my Proc only)inside the proc with extra code here

CREATE PROC sp_user (@userid INT ,@username VARCHAR (20))
AS

BEGIN
    IF EXISTS ( SELECT 1
                FROM   tbl_ins
                WHERE  UserName = @username )
    BEGIN
        RAISERROR ( 'UserName %s Already Exists.', -- Message text.
          16, -- Severity.
          1, -- State.
          @username
           );
    END
    ELSE
    BEGIN
        INSERT INTO tbl_ins
          (userid, username)
        VALUES
          (@userid, @username)
    END
END

i现在想你很清楚，回答我你的解决方案是1还是2

i think now you are clear, reply me what's your solution either 1 or 2

create proc sp_user
(
@userid int,
@username varchar(20)
)
as 
begin

Declare @Ucount int

Select @Ucount = Count(*) From tbl_ins where Userid = @Userid

IF @Ucount = 0
BEGIN
insert into tbl_ins(userid, username) values(@userid,@username)
Return 1
END
Else
return -1
end

从前端您可以显示消息

From front end you can display a message

看到：

See this:

CREATE PROCEDURE create_user
(
   @userid int,
   @username varchar(20)
)
AS
BEGIN
if exists (your query to check the existence of specified value) 
//// 
else 
 insert into tbl_ins(userid, username) values(@userid,@username)
END

这篇关于如何使用存储过程检查表中已退出的用户名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！