比较数据库中是否存在用户名 [英] comparing username present in database or not
问题描述
无论是否存在用户名,我都必须与我的数据库进行比较,如果存在,则插入新的用户名,否则显示一个消息,表明用户名已存在.还告诉我我必须在存储过程中写什么.
i have to compare with my database whether username is present or not,if present then inserting new username otherwise show a msg that username already exist. also tell me what i have to write in stored procedure.
推荐答案
--Stored procedure
Declare @username varchar(max)
Declare @retuenValue int output
if not exists (select * from User where Username = @username)
Begin
--insert statement here
set @retuenValue = scopidentity()
END
ELSE
BEGIN
set @retuenValue = 0
END
-aspx.cs页面
如果returnvalue为零,则存在用户名
-- aspx.cs page
if returnvalue is zero then username is alraedy exists
创建表:-
create table :-
CREATE TABLE [dbo].[TBL_CATEGORY_MASTER](
[pk_cat_id] [int] IDENTITY(1,1) NOT NULL,
[category_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[is_active] [bit] NULL CONSTRAINT [DF_TBL_CATEGORY_MASTER_is_active] DEFAULT ((1)),
CONSTRAINT [PK_TBL_CATEGORY_MASTER] PRIMARY KEY CLUSTERED
(
[pk_cat_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
然后
创建proc [dbo].[usp_Add_Category]
@pk_cat_id int,
@category_name varchar(50),
@active int
作为
如果存在(从TBL_CATEGORY_MASTER中选择1,其中category_name = @ category_name AND pk_cat_id<> @pk_cat_id)
开始
SELECT -1
结束
如果存在,则为ELSE(从TBL_CATEGORY_MASTER中选择1,其中pk_cat_id = @ pk_cat_id)
开始
更新TBL_CATEGORY_MASTER SET category_name = @ category_name,is_active = @ active WHERE pk_cat_id = @ pk_cat_id
选择0
结束
ELSE if(@ pk_cat_id = 0)
开始
插入到TBL_CATEGORY_MASTER值(@ category_name,@ active)中
选择@@ identity
结束
如果存储过程返回-1,则记录已经存在...
试试这个查询:)
您将得到所有答案
then
create proc [dbo].[usp_Add_Category]
@pk_cat_id int,
@category_name varchar(50),
@active int
as
if exists(select 1 from TBL_CATEGORY_MASTER where category_name=@category_name AND pk_cat_id<>@pk_cat_id)
BEGIN
SELECT -1
end
ELSE if exists (select 1 from TBL_CATEGORY_MASTER where pk_cat_id=@pk_cat_id)
BEGIN
UPDATE TBL_CATEGORY_MASTER SET category_name=@category_name,is_active=@active WHERE pk_cat_id=@pk_cat_id
SELECT 0
end
ELSE if (@pk_cat_id=0)
BEGIN
INSERT INTO TBL_CATEGORY_MASTER VALUES (@category_name,@active)
select @@identity
END
if stored procedure return -1 then record already exists...
try this query :)
you will get all your answers
这篇关于比较数据库中是否存在用户名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!