--创建一个存储过程
Create PROCEDURE [dbo].[Proc_Users_ADD_New]
--声明输入参数
@Id nvarchar(20),
@Password nvarchar(8),
@UserName nvarchar(20),
@RoleId int,
@Status char(1),
@Status_Tmp char(1),
@CreateUser nvarchar(20),
@CreateTime datetime,
@CompanyId int,
@Email nvarchar(40),
@InDate datetime,
@IsTraining int,
@ExtNumber nvarchar(15),
@IpExtNumber nvarchar(15),
@Chanel int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
开始一个事务
BEGIN TRAN TRAN1
declare @UserVersion int
set @UserVersion = 0
if (len(@Email) < 1)
begin
set @Email = null
end
if (len(@ExtNumber) < 1)
begin
set @ExtNumber = null
end
if (len(@IpExtNumber) < 1)
begin
set @IpExtNumber = null
end
--执行sql语句
INSERT INTO [Users](
[Id],[Password],[UserName],[RoleId],[Status],[Status_Tmp],[CreateUser],[CreateTime],[CompanyId],[email]
,[InDate],[IsTraining],ExtNumber,IpExtNumber,Chanel
)VALUES(
@Id,@Password,@UserName,@RoleId,@Status,@Status_Tmp,@CreateUser,@CreateTime,@CompanyId,@Email
,@InDate,@IsTraining,@ExtNumber,@IpExtNumber,@Chanel
)
/*
--同时建立RTX账号
if (not exists(select id from rtxdb.dbo.Sys_user where ID=@Id))
begin
select @UserVersion=UserVersion FROM rtxdb.dbo.SYS_SysConfig
set @UserVersion = @UserVersion + 1
Insert into rtxdb.dbo.Sys_user(ID,UserName,[Name],Pwd,Gender,UserVersion,UserRight,Mobile,Email,Phone,Face,UserType,ProfileOpenLevel,BuddyAuth,LastLogonTime,AuthType)
values(@Id,@Id,@UserName,'2ED63F22F16680FF772ECB127F67E7A000000000000000000000000000000000',0,@UserVersion,'00000000000000000000000000000000','','','',126,0,0,0,0,0)
--插入用户后
Update rtxdb.dbo.SYS_SysConfig set UserVersion=@UserVersion
--将用户插入部门
Insert into rtxdb.dbo.RTX_DeptUser(DeptID,UserID) values(21,@Id)
end
*/
IF @@ERROR<>0
--回滚事物
BEGIN
ROLLBACK TRAN TRAN1
END
ELSE
--提交事务
BEGIN
COMMIT TRAN TRAN1
END
注:
在存储过程中,经常用到SET NOCOUNT ON;
作用:阻止在结果集中返回显示受T-SQL语句或则usp影响的行计数信息。
当SET ONCOUNT ON时候,不返回计数,当SET NOCOUNT OFF时候,返回计数;
即使当SET NOCOUNT ON 时候,也更新@@RowCount;
当SET NOCOUNT on时候,将不向客户端发送存储过程每个语句的DONE_IN_proc消息,如果存储过程中包含一些并不返回实际数据的语句,网络通信流量便会大量减少,可以显著提高应用程序性能;
SET NOCOUNT 指定的设置时在执行或运行时候生效,分析时候不生效。
1 USE Northwind
2 GO
3 SET NOCOUNT OFF;
4 SELECT TOP 5 OrderDate FROM Orders
5 GO
结果集显示:
1 SET NOCOUNT ON;
2 SELECT TOP 5 OrderDate FROM Orders
3 GO
end