写个存储过程要求带参数,首先判断下UserId是不是6位的半角英文,不是的话抛出异常;然后再判断Role的值是1还是2,如果都不是抛出异常,谢谢 帮忙 我这个刚学不久create proc a @UserId char(06),
@Role char(01)
AS
DECLARE @RecValue int
DECLARE @chkError char(01)
Begin TRAN
....
....
@Role char(01)
AS
DECLARE @RecValue int
DECLARE @chkError char(01)
Begin TRAN
....
....
--存储过程
create proc a
@UserId varchar(06),
@Role varchar(01)
AS
if(patindex('%[^a-zA-Z]%',@UserId)>0)
raiserror('只许输入半角英文',16,1)
else
begin
if(@Role!=1) and ( @Role !=2)
raiserror('role只能为1或者2',16,1)
else
print 's' --做你要做的操作
end
--测试...
exec a 'f,a',1
--存储过程
create proc a
@UserId varchar(50),
@Role varchar(10)
AS
if(patindex('%[^a-zA-Z]%',@UserId)>0) or (len(@UserId)!=6)
raiserror('只许输入6位半角英文',16,1)
else
begin
if(@Role!=1) and ( @Role !=2)
raiserror('role只能为1或者2',16,1)
else
print 's' --做你要做的操作
end
--存储过程
create proc a
@UserId varchar(50),
@Role varchar(10)
AS
if(patindex('%[^a-zA-Z]%',@UserId)>0) or (len(@UserId)!=6)
raiserror('只许输入6位半角英文',16,1)
else
begin
if(@Role!=1) and ( @Role !=2)
raiserror('role只能为1或者2',16,1)
else
print 's' --做你要做的操作
end
@Userid varchar(6),
@Rolu varchar(2),
@Message varchar(200) output)
asset @Message = 'The @UserID should be 6 charactors and @Rolu should be 1 or 2!'if len(@Userid) = 6 and @Rolu = 1 or @Rolu = 2
select 'finished!'else
select @message--运行下面一句话可以得到返回值
exec testsp 'qwersd','7',''
alter proc a @UserId char(06), @Role char(01)
AS
begindeclare @str varchar(06),@num int
set @str = @UserId
set @num = 2while(len(@str)>0 and @num > 0)
begin
set @num = charindex(lower(left(@str,1)),'abcdefghigklmnopqrstuvwxyz')
set @str = stuff(@str,1,1,'')
endif(@num>0)
begin
if(@Role = '1' or @Role = '2') print 'All Right'
else print 'role只能为1或者2'--raiserror('role只能为1或者2',16,1)
endelse print 'UserID不是半角英文'--raiserror('UserID不是半角英文',16,1)endexec a'abvfds','1'exec a'abv2ds','4'
@UserId varchar(06),
@Role varchar(01)
AS
if datalength(@UserId)<=6
begin
if(patindex('%[^a-zA-Z]%',@UserId)>0)
print '只许输入半角英文'
else
begin
if(@Role!=1) and ( @Role !=2)
print 'role只能为1或者2'
else
--正常代码
end
end
else
print '只许输入6位半角英文'