create proc [dbo].[Into_RandomUser](@num int,@BindTest varchar(50))
as
declare @UserName varchar(50),@UserNum int,@randNum int,@randLetters varchar(50)
set @randNum=rand()*1000000
set @randLetters=char(95+ceiling(rand()*25))
set @UserNum=1
while (@UserNum<=@num)
begin
set @UserName=@randLetters+cast(@randNum AS varchar(50))
if(exists(select * from AssessmentSE where RandomUserName=@UserName))
else
begin
insert into dbo.AssessmentSE(AskUserName,AskDate,AskState) values(@UserName,'123',@BindTestset @UserNum=@UserNum+1
set @randNum=@randNum+1
end
end
第一个问题
上面的存储过程错在哪
错误提示:
关键字 'else' 附近有语法错误。第二个问题:
将上次改成类试asp.net中的事务要怎么改
即要么插入@num个数据 要么都不插入任何数据
begin
--以前else部分的内容
end
即要么插入 @num 条数据,要么都不插入
as
declare @UserName varchar(50),@UserNum int,@randNum int,@randLetters varchar(50)
set @randNum=rand()*1000000
set @randLetters=char(95+ceiling(rand()*25))
set @UserNum=1
while (@UserNum<=@num)
begin
set @UserName=@randLetters+cast(@randNum AS varchar(50))
if(exists(select * from AssessmentSE where RandomUserName=@UserName))
return
else
begin
insert into dbo.AssessmentSE(AskUserName,AskDate,AskState) values(@UserName,'123',@BindTestset @UserNum=@UserNum+1
set @randNum=@randNum+1
end
end
create proc [dbo].[Into_RandomUser](@num int,@BindTest varchar(50))
as
BEGIN TRANSACTION
declare @UserName varchar(50),@UserNum int,@randNum int,@randLetters varchar(50)
set @randNum=rand()*1000000
set @randLetters=char(95+ceiling(rand()*25))
set @UserNum=1
while (@UserNum<=@num)
begin
set @UserName=@randLetters+cast(@randNum AS varchar(50))
if(exists(select * from AssessmentSE where RandomUserName=@UserName))
else
begin
insert into dbo.AssessmentSE(AskUserName,AskDate,AskState) value(@UserName,'123',@BindTest)
set @UserNum=@UserNum+1
set @randNum=@randNum+1
end
if(@UserNum<>@Num)
BEGIN
print '生成失败,回滚事务'
ROLLBACK TRANSACTION
END
else
BEGIN
print '生成成功,提交事务'
COMMIT TRANSACTION
END
end没人回答
这样写行吗
(
@num INT ,
@BindTest VARCHAR(50)
)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @UserName VARCHAR(50) ,
@UserNum INT ,
@randNum INT ,
@randLetters VARCHAR(50)
SET @randNum = RAND() * 1000000
SET @randLetters = CHAR(95 + CEILING(RAND() * 25))
SET @UserNum = 1
WHILE ( @UserNum <= @num )
BEGIN
SET @UserName = @randLetters + CAST(@randNum AS VARCHAR(50))
IF ( EXISTS ( SELECT *
FROM AssessmentSE
WHERE RandomUserName = @UserName ) )
RETURN
ELSE
BEGIN
INSERT INTO dbo.AssessmentSE ( AskUserName, AskDate, AskState )
VALUES ( @UserName, '123', @BindTest )
END
END
IF XACT_STATE() = 1
COMMIT TRAN ;
ELSE
BEGIN
ROLLBACK TRAN ;
END
END TRY
BEGIN CATCH IF XACT_STATE() <> 0
ROLLBACK TRAN ;
SELECT ERROR_MESSAGE() END CATCH
END