ASPNETDB-存储过程研究(aspnet_Users_CreateUser)
aspnet_Users_CreateUser:这个存储过程主要是创建用户
1、首先检查是否存在存储过程,存在则放弃建立:
if exist(select * from dbo.sysobjects where id = object_id(N'[dbo.].[aspnet_Users_CreateUser]') and OBJECTPROPERTY(id,N'isProcedure')=1)
drop procedure
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_Users_CreateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aspnet_Users_CreateUser]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [dbo].[aspnet_Users_CreateUser]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
2、参数声明:
CREATE PROCEDURE [dbo].aspnet_Users_CreateUser
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@UserId uniqueidentifier OUTPUT
AS
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@UserId uniqueidentifier OUTPUT
AS
3、首先检查用户名是否为空、是否存在,存在则返回-1,否则,插入并返回0
BEGIN
IF( @UserId IS NULL )
--Don't understand why is NEWID()
SELECT @UserId = NEWID()
ELSE
BEGIN
--if exist, return -1
IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
WHERE @UserId = UserId ) )
RETURN -1
END
--insert data
INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
IF( @UserId IS NULL )
--Don't understand why is NEWID()
SELECT @UserId = NEWID()
ELSE
BEGIN
--if exist, return -1
IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
WHERE @UserId = UserId ) )
RETURN -1
END
--insert data
INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
4、心得:下面一句话值得学习:
BEGIN
IF(EXISTS(SELECT UserId FROM dbo.asp_Users WHERE @UserId = UserId))
RETURN -1
END
posted on 2006-03-01 09:10 mjgforever 阅读(689) 评论(1) 编辑 收藏 举报