if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_ExtendedMembership_CreateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aspnet_ExtendedMembership_CreateUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_ExtendedMembership_DeleteUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aspnet_ExtendedMembership_DeleteUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_ExtendedMembership_GetAllUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aspnet_ExtendedMembership_GetAllUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_ExtendedMembership_GetUserByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aspnet_ExtendedMembership_GetUserByUserId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_ExtendedMembership_GetUsersByUserIds]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aspnet_ExtendedMembership_GetUsersByUserIds]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_ExtendedMembership_UpdateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aspnet_ExtendedMembership_UpdateUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_ExtendedMembership_UserInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aspnet_ExtendedMembership_UserInfo]
GO

CREATE TABLE [dbo].[aspnet_ExtendedMembership_UserInfo] (
	[UserId] [uniqueidentifier] NOT NULL ,
	[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DateOfBirth] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE PROCEDURE aspnet_ExtendedMembership_CreateUser
(
	@UserId UNIQUEIDENTIFIER,
	@FirstName VARCHAR(50),
	@LastName VARCHAR(50),
	@DateOfBirth DATETIME
)
AS
BEGIN
	-- @ErrorCode to handle problems during adding a new record
	DECLARE @ErrorCode INT
	SELECT @ErrorCode = 0

        IF( EXISTS( SELECT UserId FROM dbo.aspnet_ExtendedMembership_UserInfo  WHERE @UserId = UserId ) )
	GOTO Cleanup

	INSERT INTO 
		dbo.aspnet_ExtendedMembership_UserInfo (UserId, FirstName, LastName, DateOfBirth)
	VALUES	
		(@UserId, @FirstName, @LastName, @DateOfBirth)

	SELECT @ErrorCode = @@ERROR

	IF( @ErrorCode <> 0 )
		GOTO Cleanup

	RETURN 0

Cleanup:
	SELECT @ErrorCode = -1
	RETURN @ErrorCode
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE aspnet_ExtendedMembership_DeleteUser
(
	@UserId UNIQUEIDENTIFIER,
	@NumTablesDeletedFrom INT OUTPUT
)
AS
BEGIN
	-- @ErrorCode to handle problems during deleting a record
	DECLARE @ErrorCode INT
	DECLARE @RowCount INT
	SELECT @ErrorCode = 0
	SELECT @RowCount = 0
	SELECT @NumTablesDeletedFrom = 0

	IF (@UserId IS NULL)
		GOTO Cleanup

	IF (EXISTS (SELECT UserId FROM dbo.aspnet_ExtendedMembership_UserInfo WHERE @UserId = UserId))
	BEGIN
		DELETE FROM aspnet_ExtendedMembership_UserInfo WHERE UserId = @UserId

		SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT

		IF( @ErrorCode <> 0 )
			GOTO Cleanup

		IF (@RowCount <> 0)
			SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
	END
	ELSE
		GOTO Cleanup

	RETURN 0
	
Cleanup:
	SELECT @NumTablesDeletedFrom = 0
	SELECT @ErrorCode = -1
	RETURN @ErrorCode
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE aspnet_ExtendedMembership_GetAllUsers
AS
BEGIN
	SELECT UserId, FirstName, LastName, DateOfBirth
	FROM aspnet_ExtendedMembership_UserInfo
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE aspnet_ExtendedMembership_GetUserByUserId
(
	@UserId UNIQUEIDENTIFIER
)
AS
BEGIN
	-- @ErrorCode to handle problems during retreiving of a user record
	DECLARE @ErrorCode INT
	SELECT @ErrorCode = 0

	SELECT TOP 1	FirstName, LastName, DateOfBirth
	FROM	dbo.aspnet_ExtendedMembership_UserInfo
	WHERE	UserId = @UserId

	IF (@@ROWCOUNT = 0) -- UserId not found
		GOTO Cleanup

	RETURN 0

Cleanup:
	SELECT @ErrorCode = -1
	RETURN @ErrorCode

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE aspnet_ExtendedMembership_GetUsersByUserIds
-- Procedure used to gell all users whose Ids are 
-- listed within the input list
(
	@ListOfUserIds VARCHAR(500)
)
AS
BEGIN
	DECLARE @SqlQuery VARCHAR(600)

	-- Prepare the query to retreive all user records based on the list of UserIds
	SELECT @SqlQuery = 
		'SELECT UserId, FirstName, LastName, DateOfBirth
		 FROM aspnet_ExtendedMembership_UserInfo
		 WHERE UserId IN (' + @ListOfUserIds + ')'

	-- Execute the above query
	EXEC(@SqlQuery)	
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE aspnet_ExtendedMembership_UpdateUser
(
	@UserId UNIQUEIDENTIFIER,
	@DateOfBirth DATETIME
)
AS
BEGIN
	-- @ErrorCode to handle problems during updating a record
	DECLARE @ErrorCode INT
	SELECT @ErrorCode = 0
	DECLARE @RowCount INT
	SELECT @RowCount = 0

	IF (@UserId IS NULL)
	BEGIN
		GOTO Cleanup
	END

	IF (EXISTS (SELECT UserId FROM dbo.aspnet_ExtendedMembership_UserInfo WHERE @UserId = UserId))
	BEGIN
		UPDATE	aspnet_ExtendedMembership_UserInfo
		SET		DateOfBirth = @DateOfBirth
		WHERE	UserId = @UserId
	

		SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT

		IF( @ErrorCode <> 0 )
			GOTO Cleanup

		IF (@RowCount > 0)
			RETURN @RowCount
	END

Cleanup:
	SELECT @ErrorCode = -1
	RETURN @ErrorCode

END