事情是这个样子的!
我有一个表单需要修改提交,但是修改的subject字段又不能重复所以我写了这个存储过程
/*----------------------------------------*/
/*--Author:xiaojie.shi--*/
/*--CreateTime:07-4-6--*/
CREATE PROCEDURE UP_VoteSubject_Update
@id int,
@VEID int,
@Subject nvarchar(200),
@Order int,
@SubjectType char(1),
@IsShow bit
AS
declare @r int,@r1 int;
select @r = count(subject) from VoteSubject where Subject = @Subject and [order] = @Order and SubjectType = @SubjectType and isshow = @IsShow
if @r = '1'
return '2';
--print '您未作任何修改该';
select @r1 = count(subject) from VoteSubject where subject = @Subject -- and isshow = @IsShow
if @r1 = '1'
return '3';
--print '您修改该的标题已经存在';
else if @r1 = '0'
UPDATE VoteSubject SET
[VEID] = @VEID,[Subject] = @Subject,[Order] = @Order,[SubjectType] = @SubjectType,[IsShow] = @IsShow
WHERE [id] = @id
return '1';
--print'修该成功'
GO
但是现在遇到了一个问题 就是:
当我仅仅修改比如order的时候,subject没有变,所以他会return 3
但是我还得必须保证别的修改的时候不能和已经存在的subject重复,所以还得必须验证提交的subject是否和现有的subject重复,请问我改怎么写!
我有一个表单需要修改提交,但是修改的subject字段又不能重复所以我写了这个存储过程
/*----------------------------------------*/
/*--Author:xiaojie.shi--*/
/*--CreateTime:07-4-6--*/
CREATE PROCEDURE UP_VoteSubject_Update
@id int,
@VEID int,
@Subject nvarchar(200),
@Order int,
@SubjectType char(1),
@IsShow bit
AS
declare @r int,@r1 int;
select @r = count(subject) from VoteSubject where Subject = @Subject and [order] = @Order and SubjectType = @SubjectType and isshow = @IsShow
if @r = '1'
return '2';
--print '您未作任何修改该';
select @r1 = count(subject) from VoteSubject where subject = @Subject -- and isshow = @IsShow
if @r1 = '1'
return '3';
--print '您修改该的标题已经存在';
else if @r1 = '0'
UPDATE VoteSubject SET
[VEID] = @VEID,[Subject] = @Subject,[Order] = @Order,[SubjectType] = @SubjectType,[IsShow] = @IsShow
WHERE [id] = @id
return '1';
--print'修该成功'
GO
但是现在遇到了一个问题 就是:
当我仅仅修改比如order的时候,subject没有变,所以他会return 3
但是我还得必须保证别的修改的时候不能和已经存在的subject重复,所以还得必须验证提交的subject是否和现有的subject重复,请问我改怎么写!
/*----------------------------------------*/
/*--Author:xiaojie.shi--*/
/*--CreateTime:07-4-6--*/
CREATE PROCEDURE UP_VoteSubject_Update
@id int,
@VEID int,
@Subject nvarchar(200),
@Order int,
@SubjectType char(1),
@IsShow bit
AS
declare @r int,@r1 int;
select @r = count(subject) from VoteSubject where Subject = @Subject and [order] = @Order and SubjectType = @SubjectType and isshow = @IsShow
if @r = '1'
return '2';
--print '您未作任何修改该';
select @r1 = count(subject) from VoteSubject where [id] not in (select([id]) from votesubject where subject= @subject) and subject = @Subject
if @r1 = '1'
return '3';
--print '您修改该的标题已经存在';
else if @r1 = '0'
UPDATE VoteSubject SET
[VEID] = @VEID,[Subject] = @Subject,[Order] = @Order,[SubjectType] = @SubjectType,[IsShow] = @IsShow
WHERE [id] = @id
return '1';
--print'修该成功'
GO