CREATE PROCEDURE Pr_GetUserLogin
(
	@UserName varchar(32),
	@Password varchar(255)
)

AS
	
SELECT 
	UserID
FROM
	Users
WHERE
	UserName = @UserName AND Password = @Password
~

create Procedure Pro_GetZK
(
@id int
)
as 
select zdname1,zdname2,zdname3,zdname4,zdname5,zdname6,zdname7,zdname8,zdname9
,zdname10,zdvalue1,zdvalue2,zdvalue3,zdvalue4,zdvalue5,zdvalue6,zdvalue7,zdvalue8,zdvalue9,zdvalue10
 from YX_Product where YX_ID=@id
~
CREATE Procedure Pro_ShowProduct
(
@id int
)
as
select * from YX_Product where YX_ID=@id
~
create proc YX_DeleteChuShou

@House_ID int

as


delete  YX_ChuShou where House_ID=@House_ID
~
create proc YX_DeleteChuZu
@HR_ID int
as 
delete YX_ChuZu where HR_ID=@HR_ID
	
~
create proc YX_DeleteQiuGou
@CS_ID int
as 
delete  YX_QiuGou where CS_ID=@CS_ID

~
create proc YX_DeleteQiuZu
@TE_ID int
as delete YX_QiuZu where TE_ID=@TE_ID

~
CREATE proc YX_DeleteTongXunLu
@LA_ID int
as delete  from YX_TongXunLu where LA_ID=@LA_ID
	

~
CREATE proc YX_InsertTongXunLu
(
	@U_ID int,
@U_Name varchar(50),
	@U_Type varchar (20),
	@LA_Name varchar (20),
	@LA_IDCard varchar (18),
	@LA_CityName varchar (50),
	@LA_Sex varchar (2),
	@LA_CompanyName varchar (50),
	@LA_CompanyPart varchar (50),
	@LA_Duty varchar (50),
	@LA_Type varchar (10),
	@LA_MobilePhone varchar (13),
	@LA_WorkPhone varchar (13),
	@LA_HomePhone varchar (13),
	@LA_Fax varchar (13),
	@LA_Email varchar (50),
	@LA_Address varchar (100),
	@LA_QQ varchar (10),
	@LA_MSN varchar (50),
	@LA_Blog varchar (100),
	@LA_Content varchar (2000),

	@LA_IP varchar (20),

	@LA_OrderBy int
)
as insert into YX_TongXunLu
(
	U_ID,
U_Name,
	U_Type,
	LA_Name,
	LA_IDCard,
	LA_CityName,
	LA_Sex,
	LA_CompanyName,
	LA_CompanyPart,
	LA_Duty,
	LA_Type,
	LA_MobilePhone,
	LA_WorkPhone,
	LA_HomePhone,
	LA_Fax,
	LA_Email,
	LA_Address,
	LA_QQ,
	LA_MSN,
	LA_Blog,
	LA_Content,

	LA_IP,

	LA_OrderBy

)
values
(
	@U_ID,
@U_Name,
	@U_Type,
	@LA_Name,
	@LA_IDCard,
	@LA_CityName,
	@LA_Sex,
	@LA_CompanyName,
	@LA_CompanyPart,
	@LA_Duty,
	@LA_Type,
	@LA_MobilePhone,
	@LA_WorkPhone,
	@LA_HomePhone,
	@LA_Fax,
	@LA_Email,
	@LA_Address,
	@LA_QQ,
	@LA_MSN,
	@LA_Blog,
	@LA_Content,

	@LA_IP,

	@LA_OrderBy
)
~

CREATE procedure YX_PagerList
(
    @tblName         varchar(255),              -- 
    @fldName         varchar(255),              -- ֶ
    @PageSize        int = 10,                     -- ҳߴ
    @PageIndex       int = 1,                       -- ҳ
    @IsReCount       int = 0,                       -- ؼ¼, 0 ֵ򷵻
    @OrderType       int = 0,                       -- , 0 ֵ
    @strWhere        varchar(5000) = '',      -- ѯ(ע: Ҫwhere)
    @orderfld        varchar(2550),
    @colname         varchar(5000)='*'       --ѯֶ
)
AS
begin
        declare @strSQL   varchar(5000)        -- 
	declare @strTmp   varchar(1000)          -- ʱ
	declare @strOrder varchar(4000)          -- 
	declare @strOrder2 varchar(4000)        ---

	if @OrderType != 0
	begin
		if len(@orderfld) = 0
			begin
				set @orderfld = @fldName
			end
		if  @orderfld=@fldName
		begin
			set @strTmp = '<(select min'
			set @strOrder = ' order by [' + @orderfld +'] desc'
		end 
		else
		begin
			set @strTmp = '<=(select min'
			set @strOrder = ' order by [' + @orderfld +'] desc,['+@fldName+'] desc'
		end 
		set @strOrder2 = ' order by [' + @orderfld +'] desc,['+@fldName+'] asc'
	end
	else
	begin
		
		if  @orderfld=@fldName
		begin
		     set @strTmp = '>(select max'
		     set @strOrder = ' order by [' + @orderfld +'] asc'	
		end  
		else
		begin
		     set @strTmp = '>=(select max'
		     set @strOrder = ' order by [' + @orderfld +'] asc,['+@fldName+'] asc'
		end 
		set @strOrder2 = ' order by [' + @orderfld +'] asc,['+@fldName+'] desc'
	end
      
	if @colname=''
	begin
		set @colname='*'
	end 





	if @orderfld!=@fldName 
	begin		

	set @strSQL = 'select top ' + str(@PageSize) + '  '+@colname+'  from ['
		+ @tblName + '] where [' + @orderfld + ']' + @strTmp + '(['
		+ @orderfld + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
		+ @orderfld + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
		+ 'and  '+@fldName+' not in'
		+ '(select top ' + str((@PageIndex-1)*@PageSize) + ' ['+@fldName+'] from ['+@tblName+'] '+@strOrder2+')'
		+ @strOrder2

	if @strWhere != ''
		set @strSQL = 'select top ' + str(@PageSize) + '  '+@colname+'  from ['
			+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
			+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
			+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
			+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
	end 
	else
	begin
		set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
		+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
		+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
		+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
		+ @strOrder

		if @strWhere != ''
		set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
			+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
			+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
			+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
			+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

	end 

	set @strTmp =''
	if @strWhere != ''
		set @strTmp = ' where ' + @strWhere

	if @PageIndex = 1
	begin
		set @strSQL = 'select top ' + str(@PageSize) + '  '+@colname+'  from ['
			+ @tblName + ']' + @strTmp + ' ' + @strOrder
	end

	if @IsReCount != 0
		set @strSQL = 'select count('+ @fldName +') as Total from [' + @tblName + ']'+ @strTmp
		

	 
	exec (@strSQL)
print @strSQL
end
~
CREATE proc YX_SelectQiuGou
@CS_BianHao varchar(50)
as 
select * from YX_QiuGou where CS_BianHao=@CS_BianHao
~
CREATE proc YX_SelectTongXunLu
@LA_ID int
as select * from YX_TongXunLu where LA_ID=@LA_ID
	

~

CREATE PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
    @name   sysname
AS
BEGIN
    CREATE TABLE #aspnet_RoleMembers
    (
        Group_name      sysname,
        Group_id        smallint,
        Users_in_group  sysname,
        User_id         smallint
    )

    INSERT INTO #aspnet_RoleMembers
    EXEC sp_helpuser @name

    DECLARE @user_id smallint
    DECLARE @cmd nvarchar(500)
    DECLARE c1 cursor FORWARD_ONLY FOR
        SELECT User_id FROM #aspnet_RoleMembers

    OPEN c1

    FETCH c1 INTO @user_id
    WHILE (@@fetch_status = 0)
    BEGIN
        SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
        EXEC (@cmd)
        FETCH c1 INTO @user_id
    END

    CLOSE c1
    DEALLOCATE c1
END
~


CREATE PROCEDURE [dbo].aspnet_Setup_RestorePermissions
    @name   sysname
AS
BEGIN
    DECLARE @object sysname
    DECLARE @protectType char(10)
    DECLARE @action varchar(20)
    DECLARE @grantee sysname
    DECLARE @cmd nvarchar(500)
    DECLARE c1 cursor FORWARD_ONLY FOR
        SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name

    OPEN c1

    FETCH c1 INTO @object, @protectType, @action, @grantee
    WHILE (@@fetch_status = 0)
    BEGIN
        SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
        EXEC (@cmd)
        FETCH c1 INTO @object, @protectType, @action, @grantee
    END

    CLOSE c1
    DEALLOCATE c1
END
~
CREATE Procedure GetMentList

/*ѯƵб*/
@TempID int
as
select * from YX_Channel where YX_Template=@TempID
~
CREATE PROCEDURE Pr_UpdateItemVoteCount
(
	@ItemID int
)
AS

UPDATE 
	Items
SET
	VoteCount = VoteCount + 1
WHERE
	ItemID = @ItemID
~
CREATE PROCEDURE Pr_UpdateTopicCurrent
(
	@TopicID int
)
AS

BEGIN TRAN

UPDATE
	Topics
SET
	IsCurrent = 1
WHERE
	TopicID = @TopicID

UPDATE
	Topics
SET
	IsCurrent = 0
WHERE
	TopicID <> @TopicID

COMMIT TRAN
~
create Procedure Pro_ArticleTemplte
(
@YX_CMSModelName varchar(50),
@ChannelID int,
@YX_TemplateType int
)
as
select * from YX_CMSModel where YX_CMSModelName=@YX_CMSModelName and YX_TemplateType=@YX_TemplateType and ChannelID=@ChannelID
~
create Procedure Pro_GetTemplate

(
@modename varchar(50),
@TemplateID int
)
as
select * from YX_CMSModel where YX_CMSModelName=@modename and YX_TemplateType=@TemplateID
~
CREATE Procedure Pro_deleteArticleCMSclass
(
@id int
)
as 
update YX_Article_CMSClass set yx_nextid=(select yx_nextid from YX_NewsClass where yx_id=@id) where yx_nextid=@id
update YX_Article_CMSClass set yx_previd=(select yx_previd from YX_NewsClass where yx_id=@id ) where yx_previd=@id
Update YX_Article_CMSClass set yx_child=yx_child-1 where yx_id=(select yx_parentid from YX_Article_CMSClass where yx_id=@id)
delete from YX_CMSArticle where yx_newsclass=@id
delete from YX_Article_CMSClass where yx_id=@id
~
/*-----------Ƶ----------*/
CREATE procedure YX_CMSAddChannel 
@YX_ChannelName  varchar(50),            /*Ƶ*/
@YX_ChannelExc  varchar(100),           /*Ƶע*/
@YX_ChannelType  int,                 /*Ƶ*/
@YX_ChannelMode int,                 /*Ƶ*/
@YX_ChannelDir varchar(50),        /*Ƶļ*/
@YX_ChannelStat int,               /*Ƶ״̬*/
@YX_ChannelUrl varchar(50),     /*ⲿӵַ*/
@YX_Template int
as

insert into YX_Channel (YX_ChannelName,YX_ChannelExc,YX_ChannelType,YX_ChannelMode,YX_ChannelDir,YX_ChannelStat,YX_ChannelUrl,YX_Template)
values (@YX_ChannelName,@YX_ChannelExc,@YX_ChannelType,@YX_ChannelMode,@YX_ChannelDir,@YX_ChannelStat,@YX_ChannelUrl,@YX_Template)
~
CREATE proc YX_InsertChuShou
(
        @House_BianHao varchar (50),

	@House_Type varchar (50),
	@House_CityName varchar (10),
	@U_ID int,
	@U_Name varchar (50),
	@House_Addrinfo varchar (100),
	@Area_id int,
	@House_Addressinfo varchar (100),
	@House_Size1 float,
	@House_Size2 float,

	@House_HouseType varchar (9),
	@House_InputPrice decimal(10, 2),
	@House_CountexPress varchar (50),
	@House_RepaiDegree varchar (50),
	@House_RoomNum int,
	@House_OfficeNum int,
	@House_WashNum int,
	@House_YangTaiNum int,
	@House_Use varchar (50),
	@House_Floor int,
	@House_TotalFloor int,
	@House_Right varchar(20),
	@House_BuildYear int,
	@House_Forward varchar (50),
	@House_Feature varchar (1000),
	@House_RoomSetting varchar (100),
	@House_RoomFixing varchar (100),
	@House_PouseOwnerMemo varchar (1000),
	@House_Structure varchar (10),
	@House_ExternalWall varchar (10),
	@House_StopCar varchar (50),
	@House_CellCount int,
	@House_ServicePrice float,
	@House_ServiceexPress varchar (50),
	@House_CloseArea varchar (50),
	@House_Chamber varchar (50),
	@House_School varchar (100),
	@House_Garden varchar (100),
	@House_Piazza varchar (100),
	@House_Buslineinfo varchar (100),
@House_YiLiao varchar(100),
	@House_CertID varchar (50),

	@House_ReleaseDay int,

	@House_PinYin varchar (50),
	@House_ShortPinYin varchar (20),

	@House_IsHavePicture bit,
	@House_LeaveDay int,

	@House_PublishIP varchar (50)
)
as insert into YX_ChuShou
(
        House_BianHao,

	House_Type,
	House_CityName,
	U_ID,
	U_Name,
	House_Addrinfo,
	Area_id,
	House_Addressinfo,
	House_Size1,
	House_Size2,

	House_HouseType,
	House_InputPrice,
	House_CountexPress,
	House_RepaiDegree,
	House_RoomNum,
	House_OfficeNum,
	House_WashNum,
	House_YangTaiNum,
	House_Use,
	House_Floor,
	House_TotalFloor,
	House_Right,
	House_BuildYear,
	House_Forward,
	House_Feature,
	House_RoomSetting,
	House_RoomFixing,
	House_PouseOwnerMemo,
	House_Structure,
	House_ExternalWall,
	House_StopCar,
	House_CellCount,
	House_ServicePrice,
	House_ServiceexPress,
	House_CloseArea,
	House_Chamber,
	House_School,
	House_Garden,
	House_Piazza,
	House_Buslineinfo,
House_YiLiao,
	House_CertID,

	House_ReleaseDay,

	House_PinYin,
	House_ShortPinYin,

	House_IsHavePicture,
	House_LeaveDay,

	House_PublishIP 
)
values
(
        @House_BianHao,

	@House_Type,
	@House_CityName,
	@U_ID,
	@U_Name,
	@House_Addrinfo,
	@Area_id,
	@House_Addressinfo,
	@House_Size1,
	@House_Size2,

	@House_HouseType,
	@House_InputPrice,
	@House_CountexPress,
	@House_RepaiDegree,
	@House_RoomNum,
	@House_OfficeNum,
	@House_WashNum,
	@House_YangTaiNum,
	@House_Use,
	@House_Floor,
	@House_TotalFloor,
	@House_Right,
	@House_BuildYear,
	@House_Forward,
	@House_Feature,
	@House_RoomSetting,
	@House_RoomFixing,
	@House_PouseOwnerMemo,
	@House_Structure,
	@House_ExternalWall,
	@House_StopCar,
	@House_CellCount,
	@House_ServicePrice,
	@House_ServiceexPress,
	@House_CloseArea,
	@House_Chamber,
	@House_School,
	@House_Garden,
	@House_Piazza,
	@House_Buslineinfo,
@House_YiLiao,
	@House_CertID,

	@House_ReleaseDay,

	@House_PinYin,
	@House_ShortPinYin,

	@House_IsHavePicture,
	@House_LeaveDay,

	@House_PublishIP
)

~
CREATE proc YX_InsertChuZu
(
	@U_ID int,
	@U_Name varchar (50),
	@HR_BianHao varchar (50),
	@HR_CityName varchar (50),
	@Area_ID int,
	@HR_XiaoQuDZ varchar (50),
	@HR_DetailAddress varchar (50),
	@HR_Household int,
	@HR_HouseOffice int,
	@HR_Housewash int,
	@HR_YangTai int,
	@HR_HouseSize float,

	@HR_Housefloor int,
	@HR_HousefloorTotol int,
	@HR_RepairDegree varchar (50),
	@HR_Forward varchar (50),
@HR_JiChuSS varchar(100),
	@HR_HouseShortHireTime varchar (50),
	@HR_HouseHirePrice float,
	@HR_HouseHirePriceUnit varchar (50),
	@HR_PayWay varchar (50),
	@HR_InTime varchar (50),
	@HR_Deposit varchar (100),
	@HR_BossPay varchar (50),

	@HR_Property float,
	@HR_PropertyUnit varchar (50),
	@HR_Education varchar (300),
	@HR_Traffic varchar (300),
	@HR_Medical varchar (300),
	@HR_Bussiness varchar (300),

	@HR_Features varchar (500),
	@HR_HaveKey bit,
	@HR_PublishPeriod int,
	@HR_PinYin varchar (50),
	@HR_ShortPinYin varchar (20),



	@HR_PublishIP varchar (20),

	@HR_HavePicture bit,
	@HR_Type varchar (50),
@HR_Use varchar(50)
)
as insert into YX_ChuZu
(
	U_ID,
	U_Name,
	HR_BianHao,
	HR_CityName,
	Area_ID,
	HR_XiaoQuDZ,
	HR_DetailAddress,
	HR_Household,
	HR_HouseOffice,
	HR_Housewash,
	HR_YangTai,
	HR_HouseSize,

	HR_Housefloor,
	HR_HousefloorTotol,
	HR_RepairDegree,
	HR_Forward,
HR_JiChuSS,
	HR_HouseShortHireTime,
	HR_HouseHirePrice,
	HR_HouseHirePriceUnit,
	HR_PayWay,
	HR_InTime,
	HR_Deposit,
	HR_BossPay,

	HR_Property,
	HR_PropertyUnit,
	HR_Education,
	HR_Traffic,
	HR_Medical,
	HR_Bussiness,

	HR_Features,
	HR_HaveKey,
	HR_PublishPeriod,
	HR_PinYin,
	HR_ShortPinYin,



	HR_PublishIP,

	HR_HavePicture,
	HR_Type,
HR_Use
)
values
(
	@U_ID,
	@U_Name,
	@HR_BianHao,
	@HR_CityName,
	@Area_ID,
	@HR_XiaoQuDZ,
	@HR_DetailAddress,
	@HR_Household,
	@HR_HouseOffice,
	@HR_Housewash,
	@HR_YangTai,
	@HR_HouseSize,

	@HR_Housefloor,
	@HR_HousefloorTotol,
	@HR_RepairDegree,
	@HR_Forward,
@HR_JiChuSS,
	@HR_HouseShortHireTime,
	@HR_HouseHirePrice,
	@HR_HouseHirePriceUnit,
	@HR_PayWay,
	@HR_InTime,
	@HR_Deposit,
	@HR_BossPay,

	@HR_Property,
	@HR_PropertyUnit,
	@HR_Education,
	@HR_Traffic,
	@HR_Medical,
	@HR_Bussiness,

	@HR_Features,
	@HR_HaveKey,
	@HR_PublishPeriod,
	@HR_PinYin,
	@HR_ShortPinYin,



	@HR_PublishIP,

	@HR_HavePicture,
	@HR_Type,
@HR_Use
)
~
CREATE proc YX_InsertQiuGou
(
	@U_ID int,
	@U_Name varchar (50),
	@CS_BianHao varchar (50),
	@CS_CityName varchar (20),
	
	@CS_XiaoQuDZ varchar (50),
	@CS_SmoallAreaMust bit,
	@Area_ID int,
	@CS_AreaMust bit,

	@CS_SizeMin int,
	@CS_SizeMax int,
	@CS_SizeMust bit,
	@CS_PriceMin int,
	@CS_PriceMax int,
	@CS_PriceMust bit,

	@CS_RoomNum1 int,
	@CS_RoomOffice1 int,
	@CS_RoomNum2 int,
	@CS_RoomOffice2 int,
	@CS_ROMust bit,
@CS_HouseType varchar(20),
@CS_HouseTypeMust bit,
	@CS_Use varchar (50),
	@CS_HouseUseMust bit,
	@CS_FloorMin int,
	@CS_FloorMax int,
	@CS_FloorMust bit,
	@CS_Forward varchar (50),
	@CS_ForwardMust bit,
	@CS_RepairDegree varchar (50),
	@CS_RepairDegreeMust bit,
	@CS_Right varchar (50),
	@CS_RightMust bit,
	@CS_NearSchool varchar (50),
	@CS_NearPark varchar (50),
	@CS_NearGround varchar (50),
	@CS_NearBus varchar (50),
	@CS_NearStreet varchar (50),
	@CS_NearMust bit,
	@CS_BuildYear int,
	@CS_BuildYearMust bit,
	@CS_Other varchar (500),
	@CS_PublishDay int,
	@CS_PinYin varchar (50),
	@CS_ShortPinYin varchar (50),

	@CS_Type varchar (50),

	@CS_PublisIP varchar (20)
)
as insert into YX_QiuGou
(
	U_ID,
	U_Name,
	CS_BianHao,
	CS_CityName,
	
	CS_XiaoQuDZ,
	CS_SmoallAreaMust,
	Area_ID,
	CS_AreaMust,

	CS_SizeMin,
	CS_SizeMax,
	CS_SizeMust,
	CS_PriceMin,
	CS_PriceMax,
	CS_PriceMust,

	CS_RoomNum1,
	CS_RoomOffice1,
	CS_RoomNum2,
	CS_RoomOffice2,
	CS_ROMust,
CS_HouseType,
CS_HouseTypeMust,
	CS_Use,
	CS_HouseUseMust,
	CS_FloorMin,
	CS_FloorMax,
	CS_FloorMust,
	CS_Forward,
	CS_ForwardMust,
	CS_RepairDegree,
	CS_RepairDegreeMust,
	CS_Right,
	CS_RightMust,
	CS_NearSchool,
	CS_NearPark,
	CS_NearGround,
	CS_NearBus,
	CS_NearStreet,
	CS_NearMust,
	CS_BuildYear,
	CS_BuildYearMust,
	CS_Other ,
	CS_PublishDay,
	CS_PinYin,
	CS_ShortPinYin,

	CS_Type,

	CS_PublisIP

)
values
(
	@U_ID,
	@U_Name,
	@CS_BianHao,
	@CS_CityName,
	
	@CS_XiaoQuDZ,
	@CS_SmoallAreaMust,
	@Area_ID,
	@CS_AreaMust,

	@CS_SizeMin,
	@CS_SizeMax,
	@CS_SizeMust,
	@CS_PriceMin,
	@CS_PriceMax,
	@CS_PriceMust,

	@CS_RoomNum1,
	@CS_RoomOffice1,
	@CS_RoomNum2,
	@CS_RoomOffice2,
	@CS_ROMust,
@CS_HouseType,
@CS_HouseTypeMust,
	@CS_Use,
	@CS_HouseUseMust,
	@CS_FloorMin,
	@CS_FloorMax,
	@CS_FloorMust,
	@CS_Forward,
	@CS_ForwardMust,
	@CS_RepairDegree,
	@CS_RepairDegreeMust,
	@CS_Right,
	@CS_RightMust,
	@CS_NearSchool,
	@CS_NearPark,
	@CS_NearGround,
	@CS_NearBus,
	@CS_NearStreet,
	@CS_NearMust,
	@CS_BuildYear,
	@CS_BuildYearMust,
	@CS_Other,
	@CS_PublishDay,
	@CS_PinYin,
	@CS_ShortPinYin,

	@CS_Type,

	@CS_PublisIP
)
~
CREATE proc YX_InsertQiuZu
(
	@U_ID int,
	@TE_CityName varchar (20),
	@U_Name varchar (50),
@TE_BianHao varchar (50),
	@TE_SmallArea varchar (50),
	@TE_SmallAreaMust bit,
	@Area_ID int,
	@Area_IDMust bit,
	@TE_HouseType varchar (50),
	@TE_IDMust bit,
	@TE_HouseHoldA int,
	@TE_HouseOfficeA int,
	@TE_HouseHoldB int,
	@TE_HouseOfficeB int,
	@TE_HOMust bit,
	@TE_SizeMin int,
	@TE_SizeMax int,
	@TE_SizeMust bit,
	@TE_FloorMin int,
	@TE_FloorMax int,
	@TE_FloorMust bit,
	@TE_HouseUse varchar (50),
@TE_HouseUseMust bit,
	@TE_HouseFoward varchar (50),
	@TE_ForwardMust bit,
	@TE_RepairDegree varchar (50),
	@TE_RepairDegreeMust bit,
	@TE_Infrastructure varchar (100),
	@TE_HouseInfrastructure varchar (100),
	@TE_InfrastructureMust bit,
	@TE_HouseInfrastructureMust bit,
	@TE_HireMin int,
	@TE_HireMax int,
	@TE_HirePriceMust bit,
	@TE_Validity int,
	@TE_HireMinNum varchar (50),
	@TE_Method varchar (20),
	@TE_HireContent varchar (500),
	@TE_School varchar (50),
	@TE_Park varchar (50),
	@TE_Ground varchar (50),
	@TE_Bus varchar (50),
	@TE_Street varchar (50),
	@TE_PinYin varchar (50),
	@TE_ShortPinYin varchar (20),

	@TE_IP varchar (50),

	@TE_Type varchar (50),

	@TE_IsHeZu varchar (50),
	@TE_IsHavePicture bit
)
as insert into YX_QiuZu
(
	U_ID,
	TE_CityName,
	U_Name,
TE_BianHao,
	TE_SmallArea,
	TE_SmallAreaMust,
	Area_ID,
	Area_IDMust,
	TE_HouseType,
	TE_IDMust,
	TE_HouseHoldA,
	TE_HouseOfficeA,
	TE_HouseHoldB,
	TE_HouseOfficeB,
	TE_HOMust,
	TE_SizeMin,
	TE_SizeMax,
	TE_SizeMust,
	TE_FloorMin,
	TE_FloorMax,
	TE_FloorMust,
	TE_HouseUse,
TE_HouseUseMust,
	TE_HouseFoward,
	TE_ForwardMust,
	TE_RepairDegree,
	TE_RepairDegreeMust,
	TE_Infrastructure,
	TE_HouseInfrastructure,
	TE_InfrastructureMust,
	TE_HouseInfrastructureMust,
	TE_HireMin,
	TE_HireMax,
	TE_HirePriceMust,
	TE_Validity,
	TE_HireMinNum,
	TE_Method,
	TE_HireContent,
	TE_School,
	TE_Park,
	TE_Ground,
	TE_Bus,
	TE_Street,
	TE_PinYin,
	TE_ShortPinYin,

	TE_IP,

	TE_Type,

	TE_IsHeZu,
	TE_IsHavePicture

)
values
(
	@U_ID,
	@TE_CityName,
	@U_Name,
@TE_BianHao,
	@TE_SmallArea,
	@TE_SmallAreaMust,
	@Area_ID,
	@Area_IDMust,
	@TE_HouseType,
	@TE_IDMust,
	@TE_HouseHoldA,
	@TE_HouseOfficeA,
	@TE_HouseHoldB,
	@TE_HouseOfficeB,
	@TE_HOMust,
	@TE_SizeMin,
	@TE_SizeMax,
	@TE_SizeMust,
	@TE_FloorMin,
	@TE_FloorMax,
	@TE_FloorMust,
	@TE_HouseUse,
@TE_HouseUseMust,
	@TE_HouseFoward,
	@TE_ForwardMust,
	@TE_RepairDegree,
	@TE_RepairDegreeMust,
	@TE_Infrastructure,
	@TE_HouseInfrastructure,
	@TE_InfrastructureMust,
	@TE_HouseInfrastructureMust,
	@TE_HireMin,
	@TE_HireMax,
	@TE_HirePriceMust,
	@TE_Validity,
	@TE_HireMinNum,
	@TE_Method,
	@TE_HireContent,
	@TE_School,
	@TE_Park,
	@TE_Ground,
	@TE_Bus,
	@TE_Street,
	@TE_PinYin,
	@TE_ShortPinYin,

	@TE_IP,

	@TE_Type,

	@TE_IsHeZu,
	@TE_IsHavePicture
)
~

CREATE proc YX_InsertTuPian
(
	@TP_Type varchar (50) ,
@TP_Name varchar(100),
	@TP_BianHao varchar (50) ,
	@TP_IP varchar (20) ,
	@TP_UpLoaderName varchar (50)
)
as insert into YX_TuPian(TP_Type,TP_Name,TP_BianHao,TP_IP,TP_UpLoaderName)
values
(
@TP_Type,
@TP_Name,
	@TP_BianHao,
	@TP_IP,
	@TP_UpLoaderName
)
~
CREATE proc YX_SelectChuShou
(
@House_BianHao varchar(50)
)
as
(
select * from YX_ChuShou where House_BianHao=@House_BianHao
)
      


~
CREATE proc YX_SelectChuZu
@HR_BianHao varchar(50)
as 
select * from YX_ChuZu where HR_BianHao=@HR_BianHao
~
CREATE proc YX_SelectQiuZu
@TE_BianHao varchar(50)
as select * from YX_QiuZu where TE_BianHao=@TE_BianHao
~

CREATE PROCEDURE YX_SelectSysInfo
 AS
select * from YX_SysInfo
~
CREATE proc YX_UpdateChuShou
(
@House_BianHao varchar(50),



	@House_Addrinfo varchar (100),
	@Area_id int,
	@House_Addressinfo varchar (100),
	@House_Size1 float,
	@House_Size2 float,

	@House_HouseType varchar (9),
	@House_InputPrice decimal(10, 2),
	@House_CountexPress varchar (50),
	@House_RepaiDegree varchar (50),
	@House_RoomNum int,
	@House_OfficeNum int,
	@House_WashNum int,
	@House_YangTaiNum int,
	@House_Use varchar (50),
	@House_Floor int,
	@House_TotalFloor int,
	@House_Right varchar(20),
	@House_BuildYear int,
	@House_Forward varchar (50),
	@House_Feature varchar (1000),
	@House_RoomSetting varchar (100),
	@House_RoomFixing varchar (100),
	@House_PouseOwnerMemo varchar (1000),
	@House_Structure varchar (10),
	@House_ExternalWall varchar (10),
	@House_StopCar varchar (50),
	@House_CellCount int,
	@House_ServicePrice float,
	@House_ServiceexPress varchar (50),
	@House_CloseArea varchar (50),
	@House_Chamber varchar (50),
	@House_School varchar (100),
	@House_Garden varchar (100),
	@House_Piazza varchar (100),
	@House_Buslineinfo varchar (100),
@House_YiLiao varchar(100),

	@House_CertID varchar (50),

	@House_ReleaseDay int,

	@House_PinYin varchar (50),
	@House_ShortPinYin varchar (20),

	@House_IsHavePicture bit,
@House_LastModifyTime datetime,
@House_ModifyNum int



)
as update YX_ChuShou set




	House_Addrinfo=@House_Addrinfo,
	Area_id=@Area_id,
	House_Addressinfo=@House_Addressinfo,
	House_Size1=@House_Size1,
	House_Size2=@House_Size2,

	House_HouseType=@House_HouseType,
	House_InputPrice=@House_InputPrice,
	House_CountexPress=@House_CountexPress,
	House_RepaiDegree=@House_RepaiDegree,
	House_RoomNum=@House_RoomNum,
	House_OfficeNum=@House_OfficeNum,
	House_WashNum=@House_WashNum,
	House_YangTaiNum=@House_YangTaiNum,
	House_Use=@House_Use,
	House_Floor=@House_Floor,
	House_TotalFloor=@House_TotalFloor,
	House_Right=@House_Right,
	House_BuildYear=@House_BuildYear,
	House_Forward=@House_Forward,
	House_Feature=@House_Feature,
	House_RoomSetting=@House_RoomSetting,
	House_RoomFixing=@House_RoomFixing,
	House_PouseOwnerMemo=@House_PouseOwnerMemo,
	House_Structure=@House_Structure,
	House_ExternalWall=@House_ExternalWall,
	House_StopCar=@House_StopCar,
	House_CellCount=@House_CellCount,
	House_ServicePrice=@House_ServicePrice,
	House_ServiceexPress=@House_ServiceexPress,
	House_CloseArea=@House_CloseArea,
	House_Chamber=@House_Chamber,
	House_School=@House_School,
	House_Garden=@House_Garden,
	House_Piazza=@House_Piazza,
	House_Buslineinfo=@House_Buslineinfo,
House_YiLiao=@House_YiLiao,

	House_CertID=@House_CertID,

	House_ReleaseDay=@House_ReleaseDay,
	
	House_PinYin=@House_PinYin,
	House_ShortPinYin=@House_ShortPinYin,

	House_IsHavePicture=@House_IsHavePicture,
House_LastModifyTime=@House_LastModifyTime,
House_ModifyNum=@House_ModifyNum




where House_BianHao=@House_BianHao
~
CREATE proc YX_UpdateChuZu
(
@HR_BianHao varchar(50),


	@Area_ID int,
	@HR_XiaoQuDZ varchar (50),
	@HR_DetailAddress varchar (50),
	@HR_Household int,
	@HR_HouseOffice int,
	@HR_Housewash int,
	@HR_YangTai int,
	@HR_HouseSize float,

	@HR_Housefloor int,
	@HR_HousefloorTotol int,
	@HR_RepairDegree varchar (50),
	@HR_Forward varchar (50),
@HR_JiChuSS varchar(100),
	@HR_HouseShortHireTime varchar (50),
	@HR_HouseHirePrice float,
	@HR_HouseHirePriceUnit varchar (50),
	@HR_PayWay varchar (50),
	@HR_InTime varchar (50),
	@HR_Deposit varchar (100),
	@HR_BossPay varchar (50),

	@HR_Property float,
	@HR_PropertyUnit varchar (50),
	@HR_Education varchar (300),
	@HR_Traffic varchar (300),
	@HR_Medical varchar (300),
	@HR_Bussiness varchar (300),

	@HR_Features varchar (500),
	@HR_HaveKey bit,
	@HR_PublishPeriod int,
	@HR_PinYin varchar (50),
	@HR_ShortPinYin varchar (20),



	@HR_LasrModifyTime datetime,
	@HR_ModifyNum int,

	@HR_HavePicture bit,
@HR_Use varchar(50)

)
as update YX_ChuZu set


	Area_ID=@Area_ID,
	HR_XiaoQuDZ=@HR_XiaoQuDZ,
	HR_DetailAddress=@HR_DetailAddress,
	HR_Household=@HR_Household,
	HR_HouseOffice=@HR_HouseOffice,
	HR_Housewash=@HR_Housewash,
	HR_YangTai=@HR_YangTai,
	HR_HouseSize=@HR_HouseSize,

	HR_Housefloor=@HR_Housefloor,
	HR_HousefloorTotol=@HR_HousefloorTotol,
	HR_RepairDegree=@HR_RepairDegree,
	HR_Forward=@HR_Forward,
HR_JiChuSS=@HR_JiChuSS,
	HR_HouseShortHireTime=@HR_HouseShortHireTime,
	HR_HouseHirePrice=@HR_HouseHirePrice,
	HR_HouseHirePriceUnit=@HR_HouseHirePriceUnit,
	HR_PayWay=@HR_PayWay,
	HR_InTime=@HR_InTime,
	HR_Deposit=@HR_Deposit,
	HR_BossPay=@HR_BossPay,

	HR_Property=@HR_Property,
	HR_PropertyUnit=@HR_PropertyUnit,
	HR_Education=@HR_Education,
	HR_Traffic=@HR_Traffic,
	HR_Medical=@HR_Medical,
	HR_Bussiness=@HR_Bussiness,

	HR_Features=@HR_Features,
	HR_HaveKey=@HR_HaveKey,
	HR_PublishPeriod=@HR_PublishPeriod,
	HR_PinYin=@HR_PinYin,
	HR_ShortPinYin=@HR_ShortPinYin,



	HR_LasrModifyTime=@HR_LasrModifyTime,
	HR_ModifyNum=@HR_ModifyNum,

	HR_HavePicture=@HR_HavePicture,
HR_Use=@HR_Use

where HR_BianHao=@HR_BianHao
~
CREATE proc YX_UpdateQiuGou
(
@CS_BianHao varchar(50),

	
	@CS_XiaoQuDZ varchar (50),
	@CS_SmoallAreaMust bit,
	@Area_ID int,
	@CS_AreaMust bit,
	
	@CS_SizeMin int,
	@CS_SizeMax int,
	@CS_SizeMust bit,
	@CS_PriceMin int,
	@CS_PriceMax int,
	@CS_PriceMust bit,
	
	@CS_RoomNum1 int,
	@CS_RoomOffice1 int,
	@CS_RoomNum2 int,
	@CS_RoomOffice2 int,
	@CS_ROMust bit,
@CS_HouseType varchar(20),
@CS_HouseTypeMust bit,
	@CS_Use varchar (50),
	@CS_HouseUseMust bit,
	@CS_FloorMin int,
	@CS_FloorMax int,
	@CS_FloorMust bit,
	@CS_Forward varchar (50),
	@CS_ForwardMust bit,
	@CS_RepairDegree varchar (50),
	@CS_RepairDegreeMust bit,
	@CS_Right varchar (50),
	@CS_RightMust bit,
	@CS_NearSchool varchar (50),
	@CS_NearPark varchar (50),
	@CS_NearGround varchar (50),
	@CS_NearBus varchar (50),
	@CS_NearStreet varchar (50),
	@CS_NearMust bit,
	@CS_BuildYear int,
	@CS_BuildYearMust bit,
	@CS_Other varchar (500),
	@CS_PublishDay int,
	@CS_PinYin varchar (50),
	@CS_ShortPinYin varchar (50),
@CS_lastModifyTime DateTime,
@CS_ModifyNum int

)

as update  YX_QiuGou set 
	

	
	CS_XiaoQuDZ=@CS_XiaoQuDZ,
	CS_SmoallAreaMust=@CS_SmoallAreaMust,
	Area_ID=@Area_ID,
	CS_AreaMust=@CS_AreaMust,
	
	CS_SizeMin=@CS_SizeMin,
	CS_SizeMax=@CS_SizeMax,
	CS_SizeMust=@CS_SizeMust,
	CS_PriceMin=@CS_PriceMin,
	CS_PriceMax=@CS_PriceMax,
	CS_PriceMust=@CS_PriceMust,
	
	CS_RoomNum1=@CS_RoomNum1,
	CS_RoomOffice1=@CS_RoomOffice1,
	CS_RoomNum2=@CS_RoomNum2,
	CS_RoomOffice2=@CS_RoomOffice2,
	CS_ROMust=@CS_ROMust,
CS_HouseType=@CS_HouseType,
CS_HouseTypeMust=@CS_HouseTypeMust,
	CS_Use=@CS_Use,
	CS_HouseUseMust=@CS_HouseUseMust,
	CS_FloorMin=@CS_FloorMin,
	CS_FloorMax=@CS_FloorMax,
	CS_FloorMust=@CS_FloorMust,
	CS_Forward=@CS_Forward,
	CS_ForwardMust=@CS_ForwardMust,
	CS_RepairDegree=@CS_RepairDegree,
	CS_RepairDegreeMust=@CS_RepairDegreeMust,
	CS_Right=@CS_Right,
	CS_RightMust=@CS_RightMust,
	CS_NearSchool=@CS_NearSchool,
	CS_NearPark=@CS_NearPark,
	CS_NearGround=@CS_NearGround,
	CS_NearBus=@CS_NearBus,
	CS_NearStreet=@CS_NearStreet,
	CS_NearMust=@CS_NearMust,
	CS_BuildYear=@CS_BuildYear,
	CS_BuildYearMust=@CS_BuildYearMust,
	CS_Other=@CS_Other,
	CS_PublishDay=@CS_PublishDay,
	CS_PinYin=@CS_PinYin,
	CS_ShortPinYin=@CS_ShortPinYin,

CS_lastModifyTime=@CS_lastModifyTime,
CS_ModifyNum=@CS_ModifyNum


where CS_BianHao=@CS_BianHao
~
CREATE proc YX_UpdateQiuZu
(
@TE_BianHao varchar(50),


	@TE_SmallArea varchar (50),
	@TE_SmallAreaMust bit,
	@Area_ID int,
	@Area_IDMust bit,
	@TE_HouseType varchar (50),
	@TE_IDMust bit,
	@TE_HouseHoldA int,
	@TE_HouseOfficeA int,
	@TE_HouseHoldB int,
	@TE_HouseOfficeB int,
	@TE_HOMust bit,
	@TE_SizeMin int,
	@TE_SizeMax int,
	@TE_SizeMust bit,
	@TE_FloorMin int,
	@TE_FloorMax int,
	@TE_FloorMust bit,
	@TE_HouseUse varchar (50),
@TE_HouseUseMust bit,
	@TE_HouseFoward varchar (50),
	@TE_ForwardMust bit,
	@TE_RepairDegree varchar (50),
	@TE_RepairDegreeMust bit,
	@TE_Infrastructure varchar (100),
	@TE_HouseInfrastructure varchar (100),
	@TE_InfrastructureMust bit,
	@TE_HouseInfrastructureMust bit,
	@TE_HireMin int,
	@TE_HireMax int,
	@TE_HirePriceMust bit,
	@TE_Validity int,
	@TE_HireMinNum varchar (50),
	@TE_Method varchar (20),
	@TE_HireContent varchar (500),
	@TE_School varchar (50),
	@TE_Park varchar (50),
	@TE_Ground varchar (50),
	@TE_Bus varchar (50),
	@TE_Street varchar (50),
	@TE_PinYin varchar (50),
	@TE_ShortPinYin varchar (20),

	@TE_LastModifyTime datetime,
	@TE_ModifyNum int,
	@TE_Type varchar (50),

	@TE_IsHeZu varchar (50),
	@TE_IsHavePicture bit
)
as update YX_QiuZu set 

	
	TE_SmallArea=@TE_SmallArea,
	TE_SmallAreaMust=@TE_SmallAreaMust,
	Area_ID=@Area_ID,
	Area_IDMust=@Area_IDMust,
	TE_HouseType=@TE_HouseType,
	TE_IDMust=@TE_IDMust,
	TE_HouseHoldA=@TE_HouseHoldA,
	TE_HouseOfficeA=@TE_HouseOfficeA,
	TE_HouseHoldB=@TE_HouseHoldB,
	TE_HouseOfficeB=@TE_HouseOfficeB,
	TE_HOMust=@TE_HOMust,
	TE_SizeMin=@TE_SizeMin,
	TE_SizeMax=@TE_SizeMax,
	TE_SizeMust=@TE_SizeMust,
	TE_FloorMin=@TE_FloorMin,
	TE_FloorMax=@TE_FloorMax,
	TE_FloorMust=@TE_FloorMust,
	TE_HouseUse=@TE_HouseUse,
TE_HouseUseMust=@TE_HouseUseMust,
	TE_HouseFoward=@TE_HouseFoward,
	TE_ForwardMust=@TE_ForwardMust,
	TE_RepairDegree=@TE_RepairDegree,
	TE_RepairDegreeMust=@TE_RepairDegreeMust,
	TE_Infrastructure=@TE_Infrastructure,
	TE_HouseInfrastructure=@TE_HouseInfrastructure,
	TE_InfrastructureMust=@TE_InfrastructureMust,
	TE_HouseInfrastructureMust=@TE_HouseInfrastructureMust,
	TE_HireMin=@TE_HireMin,
	TE_HireMax=@TE_HireMax,
	TE_HirePriceMust=@TE_HirePriceMust,
	TE_Validity=@TE_Validity,
	TE_HireMinNum=@TE_HireMinNum,
	TE_Method=@TE_Method,
	TE_HireContent=@TE_HireContent,
	TE_School=@TE_School,
	TE_Park=@TE_Park,
	TE_Ground=@TE_Ground,
	TE_Bus=@TE_Bus,
	TE_Street=@TE_Street,
	TE_PinYin=@TE_PinYin,
	TE_ShortPinYin=@TE_ShortPinYin,


	TE_LastModifyTime=@TE_LastModifyTime,
	TE_ModifyNum=@TE_ModifyNum,
	TE_Type=@TE_Type,


	TE_IsHeZu=@TE_IsHeZu,
	TE_IsHavePicture=@TE_IsHavePicture

where TE_BianHao=@TE_BianHao
~


CREATE PROCEDURE YX_UpdateSysInfo
(
@_sys_name varchar(50),
@_sys_url varchar(50),
@_sys_keywords varchar(50),
@_sys_des varchar(50),
@_sys_email varchar(50),
@_sys_addr varchar(50),
@_sys_tel varchar(30),
@_sys_fax varchar(30),
@_sys_mobile varchar(30),
@_sys_post varchar(30),
@_sys_copyright varchar(50),
@_sys_issendemail  Int,
@_sys_isclose Int,
@_sys_isclosedes  text,
@_sys_ispersonreg  Int,
@_sys_iscentercomreg Int,
@_sys_ishousesalereg Int
)
AS
update YX_SysInfo set  sys_name=@_sys_name,sys_url =@_sys_url ,sys_keywords=@_sys_keywords,sys_des=@_sys_des,sys_email=@_sys_email,sys_addr=@_sys_addr,sys_tel=@_sys_tel,sys_fax=@_sys_fax,sys_mobile=@_sys_mobile,sys_post=@_sys_post,sys_copyright=@_sys_copyright,sys_issendemail=@_sys_issendemail,sys_isclose=@_sys_isclose,sys_isclosedes=@_sys_isclosedes,sys_ispersonreg=@_sys_ispersonreg,sys_iscentercomreg=@_sys_iscentercomreg,sys_ishousesalereg=@_sys_ishousesalereg
~
CREATE proc YX_UpdateTongXunLu
(
@LA_ID int,

	@LA_Name varchar (20),
	@LA_IDCard varchar (18),
	@LA_CityName varchar (50),
	@LA_Sex varchar (20),
	@LA_CompanyName varchar (50),
	@LA_CompanyPart varchar (50),
	@LA_Duty varchar (50),
	@LA_Type varchar (10),
	@LA_MobilePhone varchar (13),
	@LA_WorkPhone varchar (13),
	@LA_HomePhone varchar (13),
	@LA_Fax varchar (13),
	@LA_Email varchar (50),
	@LA_Address varchar (100),
	@LA_QQ varchar (10),
	@LA_MSN varchar (50),
	@LA_Blog varchar (100),
	@LA_Content varchar (2000),

	@LA_LastModifyTime datetime,
	@LA_ModifyNum int,


	@LA_OrderBy int
)
as update YX_TongXunLu set 
	

	LA_Name=@LA_Name,
	LA_IDCard=@LA_IDCard,
	LA_CityName=@LA_CityName,
	LA_Sex=@LA_Sex,
	LA_CompanyName=@LA_CompanyName,
	LA_CompanyPart=@LA_CompanyPart,
	LA_Duty=@LA_Duty,
	LA_Type=@LA_Type,
	LA_MobilePhone=@LA_MobilePhone,
	LA_WorkPhone=@LA_WorkPhone,
	LA_HomePhone=@LA_HomePhone,
	LA_Fax=@LA_Fax,
	LA_Email=@LA_Email,
	LA_Address=@LA_Address,
	LA_QQ=@LA_QQ,
	LA_MSN=@LA_MSN,
	LA_Blog=@LA_Blog,
	LA_Content=@LA_Content,

	LA_LastModifyTime=@LA_LastModifyTime,
	LA_ModifyNum=@LA_ModifyNum,

	LA_OrderBy=@LA_OrderBy

where LA_ID=@LA_ID
~

CREATE PROCEDURE [dbo].aspnet_Applications_CreateApplication
    @ApplicationName      nvarchar(256),
    @ApplicationId        uniqueidentifier OUTPUT
AS
BEGIN
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

    IF(@ApplicationId IS NULL)
    BEGIN
        DECLARE @TranStarted   bit
        SET @TranStarted = 0

        IF( @@TRANCOUNT = 0 )
        BEGIN
	        BEGIN TRANSACTION
	        SET @TranStarted = 1
        END
        ELSE
    	    SET @TranStarted = 0

        SELECT  @ApplicationId = ApplicationId
        FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
        WHERE LOWER(@ApplicationName) = LoweredApplicationName

        IF(@ApplicationId IS NULL)
        BEGIN
            SELECT  @ApplicationId = NEWID()
            INSERT  dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
            VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
        END


        IF( @TranStarted = 1 )
        BEGIN
            IF(@@ERROR = 0)
            BEGIN
	        SET @TranStarted = 0
	        COMMIT TRANSACTION
            END
            ELSE
            BEGIN
                SET @TranStarted = 0
                ROLLBACK TRANSACTION
            END
        END
    END
END
~


CREATE PROCEDURE [dbo].aspnet_CheckSchemaVersion
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
    IF (EXISTS( SELECT  *
                FROM    dbo.aspnet_SchemaVersions
                WHERE   Feature = LOWER( @Feature ) AND
                        CompatibleSchemaVersion = @CompatibleSchemaVersion ))
        RETURN 0

    RETURN 1
END
~
CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
    @ApplicationName       nvarchar(256),
    @EmailToMatch          nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    IF( @EmailToMatch IS NULL )
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT u.UserId
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
            ORDER BY m.LoweredEmail
    ELSE
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT u.UserId
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
            ORDER BY m.LoweredEmail

    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY m.LoweredEmail

    SELECT  @TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
    RETURN @TotalRecords
END
~
CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByName
    @ApplicationName       nvarchar(256),
    @UserNameToMatch       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT u.UserId
        FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
        ORDER BY u.UserName


    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName

    SELECT  @TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
    RETURN @TotalRecords
END
~
CREATE PROCEDURE dbo.aspnet_Membership_GetAllUsers
    @ApplicationName       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0


    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
    SELECT u.UserId
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
    ORDER BY u.UserName

    SELECT @TotalRecords = @@ROWCOUNT

    SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName
    RETURN @TotalRecords
END
~
CREATE PROCEDURE dbo.aspnet_Personalization_GetApplicationId (
    @ApplicationName NVARCHAR(256),
    @ApplicationId UNIQUEIDENTIFIER OUT)
AS
BEGIN
    SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
END
~

CREATE PROCEDURE dbo.aspnet_Profile_GetProfiles
    @ApplicationName        nvarchar(256),
    @ProfileAuthOptions     int,
    @PageIndex              int,
    @PageSize               int,
    @UserNameToMatch        nvarchar(256) = NULL,
    @InactiveSinceDate      datetime      = NULL
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT  u.UserId
        FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
        WHERE   ApplicationId = @ApplicationId
            AND u.UserId = p.UserId
            AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
            AND (     (@ProfileAuthOptions = 2)
                   OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                   OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                 )
            AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
        ORDER BY UserName

    SELECT  u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
            DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
    WHERE   u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound

    SELECT COUNT(*)
    FROM   #PageIndexForUsers

    DROP TABLE #PageIndexForUsers
END
~

CREATE PROCEDURE [dbo].aspnet_RegisterSchemaVersion
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128),
    @IsCurrentVersion          bit,
    @RemoveIncompatibleSchema  bit
AS
BEGIN
    IF( @RemoveIncompatibleSchema = 1 )
    BEGIN
        DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
    END
    ELSE
    BEGIN
        IF( @IsCurrentVersion = 1 )
        BEGIN
            UPDATE dbo.aspnet_SchemaVersions
            SET IsCurrentVersion = 0
            WHERE Feature = LOWER( @Feature )
        END
    END

    INSERT  dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
    VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
END
~

CREATE PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
    DELETE FROM dbo.aspnet_SchemaVersions
        WHERE   Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
END
~
CREATE PROCEDURE dbo.aspnet_WebEvent_LogEvent
        @EventId         char(32),
        @EventTimeUtc    datetime,
        @EventTime       datetime,
        @EventType       nvarchar(256),
        @EventSequence   decimal(19,0),
        @EventOccurrence decimal(19,0),
        @EventCode       int,
        @EventDetailCode int,
        @Message         nvarchar(1024),
        @ApplicationPath nvarchar(256),
        @ApplicationVirtualPath nvarchar(256),
        @MachineName    nvarchar(256),
        @RequestUrl      nvarchar(1024),
        @ExceptionType   nvarchar(256),
        @Details         ntext
AS
BEGIN
    INSERT
        dbo.aspnet_WebEvent_Events
        (
            EventId,
            EventTimeUtc,
            EventTime,
            EventType,
            EventSequence,
            EventOccurrence,
            EventCode,
            EventDetailCode,
            Message,
            ApplicationPath,
            ApplicationVirtualPath,
            MachineName,
            RequestUrl,
            ExceptionType,
            Details
        )
    VALUES
    (
        @EventId,
        @EventTimeUtc,
        @EventTime,
        @EventType,
        @EventSequence,
        @EventOccurrence,
        @EventCode,
        @EventDetailCode,
        @Message,
        @ApplicationPath,
        @ApplicationVirtualPath,
        @MachineName,
        @RequestUrl,
        @ExceptionType,
        @Details
    )
END
~
CREATE PROCEDURE dbo.aspnet_Paths_CreatePath
    @ApplicationId UNIQUEIDENTIFIER,
    @Path           NVARCHAR(256),
    @PathId         UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    BEGIN TRANSACTION
    IF (NOT EXISTS(SELECT * FROM dbo.aspnet_Paths WHERE LoweredPath = LOWER(@Path) AND ApplicationId = @ApplicationId))
    BEGIN
        INSERT dbo.aspnet_Paths (ApplicationId, Path, LoweredPath) VALUES (@ApplicationId, @Path, LOWER(@Path))
    END
    COMMIT TRANSACTION
    SELECT @PathId = PathId FROM dbo.aspnet_Paths WHERE LOWER(@Path) = LoweredPath AND ApplicationId = @ApplicationId
END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_FindState (
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @PageIndex              INT,
    @PageSize               INT,
    @Path NVARCHAR(256) = NULL,
    @UserName NVARCHAR(256) = NULL,
    @InactiveSinceDate DATETIME = NULL)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        RETURN

    -- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table to store the selected results
    CREATE TABLE #PageIndex (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ItemId UNIQUEIDENTIFIER
    )

    IF (@AllUsersScope = 1)
    BEGIN
        -- Insert into our temp table
        INSERT INTO #PageIndex (ItemId)
        SELECT Paths.PathId
        FROM dbo.aspnet_Paths Paths,
             ((SELECT Paths.PathId
               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
               WHERE Paths.ApplicationId = @ApplicationId
                      AND AllUsers.PathId = Paths.PathId
                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              ) AS SharedDataPerPath
              FULL OUTER JOIN
              (SELECT DISTINCT Paths.PathId
               FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Paths Paths
               WHERE Paths.ApplicationId = @ApplicationId
                      AND PerUser.PathId = Paths.PathId
                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              ) AS UserDataPerPath
              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
             )
        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
        ORDER BY Paths.Path ASC

        SELECT @TotalRecords = @@ROWCOUNT

        SELECT Paths.Path,
               SharedDataPerPath.LastUpdatedDate,
               SharedDataPerPath.SharedDataLength,
               UserDataPerPath.UserDataLength,
               UserDataPerPath.UserCount
        FROM dbo.aspnet_Paths Paths,
             ((SELECT PageIndex.ItemId AS PathId,
                      AllUsers.LastUpdatedDate AS LastUpdatedDate,
                      DATALENGTH(AllUsers.PageSettings) AS SharedDataLength
               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, #PageIndex PageIndex
               WHERE AllUsers.PathId = PageIndex.ItemId
                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
              ) AS SharedDataPerPath
              FULL OUTER JOIN
              (SELECT PageIndex.ItemId AS PathId,
                      SUM(DATALENGTH(PerUser.PageSettings)) AS UserDataLength,
                      COUNT(*) AS UserCount
               FROM aspnet_PersonalizationPerUser PerUser, #PageIndex PageIndex
               WHERE PerUser.PathId = PageIndex.ItemId
                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
               GROUP BY PageIndex.ItemId
              ) AS UserDataPerPath
              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
             )
        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
        ORDER BY Paths.Path ASC
    END
    ELSE
    BEGIN
        -- Insert into our temp table
        INSERT INTO #PageIndex (ItemId)
        SELECT PerUser.Id
        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
        WHERE Paths.ApplicationId = @ApplicationId
              AND PerUser.UserId = Users.UserId
              AND PerUser.PathId = Paths.PathId
              AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
              AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
        ORDER BY Paths.Path ASC, Users.UserName ASC

        SELECT @TotalRecords = @@ROWCOUNT

        SELECT Paths.Path, PerUser.LastUpdatedDate, DATALENGTH(PerUser.PageSettings), Users.UserName, Users.LastActivityDate
        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths, #PageIndex PageIndex
        WHERE PerUser.Id = PageIndex.ItemId
              AND PerUser.UserId = Users.UserId
              AND PerUser.PathId = Paths.PathId
              AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
        ORDER BY Paths.Path ASC, Users.UserName ASC
    END

    RETURN @TotalRecords
END
~
CREATE PROCEDURE dbo.aspnet_Roles_CreateRole
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    INSERT INTO dbo.aspnet_Roles
                (ApplicationId, RoleName, LoweredRoleName)
         VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN(0)

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
~

CREATE PROCEDURE dbo.aspnet_Roles_GetAllRoles (
    @ApplicationName           nvarchar(256))
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN
    SELECT RoleName
    FROM   dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
    ORDER BY RoleName
END
~

CREATE PROCEDURE dbo.aspnet_Roles_RoleExists
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(0)
    IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
        RETURN(1)
    ELSE
        RETURN(0)
END
~

CREATE PROCEDURE [dbo].aspnet_Users_CreateUser
    @ApplicationId    uniqueidentifier,
    @UserName         nvarchar(256),
    @IsUserAnonymous  bit,
    @LastActivityDate DATETIME,
    @UserId           uniqueidentifier OUTPUT
AS
BEGIN
    IF( @UserId IS NULL )
        SELECT @UserId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
                    WHERE @UserId = UserId ) )
            RETURN -1
    END

    INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
    VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)

    RETURN 0
END
~
CREATE PROCEDURE [dbo].aspnet_AnyDataInTables
    @TablesToCheck int
AS
BEGIN
    -- Check Membership table if (@TablesToCheck & 1) is set
    IF ((@TablesToCheck & 1) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
        BEGIN
            SELECT N'aspnet_Membership'
            RETURN
        END
    END

    -- Check aspnet_Roles table if (@TablesToCheck & 2) is set
    IF ((@TablesToCheck & 2) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Roles') AND (type = 'V'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
        BEGIN
            SELECT N'aspnet_Roles'
            RETURN
        END
    END

    -- Check aspnet_Profile table if (@TablesToCheck & 4) is set
    IF ((@TablesToCheck & 4) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
        BEGIN
            SELECT N'aspnet_Profile'
            RETURN
        END
    END

    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
    IF ((@TablesToCheck & 8) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
        BEGIN
            SELECT N'aspnet_PersonalizationPerUser'
            RETURN
        END
    END

    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
    IF ((@TablesToCheck & 16) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_WebEvent_LogEvent') AND (type = 'P'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
        BEGIN
            SELECT N'aspnet_WebEvent_Events'
            RETURN
        END
    END

    -- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
    IF ((@TablesToCheck & 1) <> 0 AND
        (@TablesToCheck & 2) <> 0 AND
        (@TablesToCheck & 4) <> 0 AND
        (@TablesToCheck & 8) <> 0 AND
        (@TablesToCheck & 32) <> 0 AND
        (@TablesToCheck & 128) <> 0 AND
        (@TablesToCheck & 256) <> 0 AND
        (@TablesToCheck & 512) <> 0 AND
        (@TablesToCheck & 1024) <> 0)
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
        BEGIN
            SELECT N'aspnet_Users'
            RETURN
        END
        IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
        BEGIN
            SELECT N'aspnet_Applications'
            RETURN
        END
    END
END
~
CREATE PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
    @ApplicationName       nvarchar(256),
    @UserName              nvarchar(256),
    @NewPasswordQuestion   nvarchar(256),
    @NewPasswordAnswer     nvarchar(128)
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId
    IF (@UserId IS NULL)
    BEGIN
        RETURN(1)
    END

    UPDATE dbo.aspnet_Membership
    SET    PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
    WHERE  UserId=@UserId
    RETURN(0)
END
~
CREATE PROCEDURE dbo.aspnet_Membership_CreateUser
    @ApplicationName                        nvarchar(256),
    @UserName                               nvarchar(256),
    @Password                               nvarchar(128),
    @PasswordSalt                           nvarchar(128),
    @Email                                  nvarchar(256),
    @PasswordQuestion                       nvarchar(256),
    @PasswordAnswer                         nvarchar(128),
    @IsApproved                             bit,
    @CurrentTimeUtc                         datetime,
    @CreateDate                             datetime = NULL,
    @UniqueEmail                            int      = 0,
    @PasswordFormat                         int      = 0,
    @UserId                                 uniqueidentifier OUTPUT
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @NewUserId uniqueidentifier
    SELECT @NewUserId = NULL

    DECLARE @IsLockedOut bit
    SET @IsLockedOut = 0

    DECLARE @LastLockoutDate  datetime
    SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAttemptCount int
    SET @FailedPasswordAttemptCount = 0

    DECLARE @FailedPasswordAttemptWindowStart  datetime
    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAnswerAttemptCount int
    SET @FailedPasswordAnswerAttemptCount = 0

    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @NewUserCreated bit
    DECLARE @ReturnValue   int
    SET @ReturnValue = 0

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    SET @CreateDate = @CurrentTimeUtc

    SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
    IF ( @NewUserId IS NULL )
    BEGIN
        SET @NewUserId = @UserId
        EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
        SET @NewUserCreated = 1
    END
    ELSE
    BEGIN
        SET @NewUserCreated = 0
        IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
        BEGIN
            SET @ErrorCode = 6
            GOTO Cleanup
        END
    END

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @ReturnValue = -1 )
    BEGIN
        SET @ErrorCode = 10
        GOTO Cleanup
    END

    IF ( EXISTS ( SELECT UserId
                  FROM   dbo.aspnet_Membership
                  WHERE  @NewUserId = UserId ) )
    BEGIN
        SET @ErrorCode = 6
        GOTO Cleanup
    END

    SET @UserId = @NewUserId

    IF (@UniqueEmail = 1)
    BEGIN
        IF (EXISTS (SELECT *
                    FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
                    WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
        BEGIN
            SET @ErrorCode = 7
            GOTO Cleanup
        END
    END

    IF (@NewUserCreated = 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate = @CreateDate
        WHERE  @UserId = UserId
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    INSERT INTO dbo.aspnet_Membership
                ( ApplicationId,
                  UserId,
                  Password,
                  PasswordSalt,
                  Email,
                  LoweredEmail,
                  PasswordQuestion,
                  PasswordAnswer,
                  PasswordFormat,
                  IsApproved,
                  IsLockedOut,
                  CreateDate,
                  LastLoginDate,
                  LastPasswordChangedDate,
                  LastLockoutDate,
                  FailedPasswordAttemptCount,
                  FailedPasswordAttemptWindowStart,
                  FailedPasswordAnswerAttemptCount,
                  FailedPasswordAnswerAttemptWindowStart )
         VALUES ( @ApplicationId,
                  @UserId,
                  @Password,
                  @PasswordSalt,
                  @Email,
                  LOWER(@Email),
                  @PasswordQuestion,
                  @PasswordAnswer,
                  @PasswordFormat,
                  @IsApproved,
                  @IsLockedOut,
                  @CreateDate,
                  @CreateDate,
                  @CreateDate,
                  @LastLockoutDate,
                  @FailedPasswordAttemptCount,
                  @FailedPasswordAttemptWindowStart,
                  @FailedPasswordAnswerAttemptCount,
                  @FailedPasswordAnswerAttemptWindowStart )

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
~
CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
    @ApplicationName            nvarchar(256),
    @MinutesSinceLastInActive   int,
    @CurrentTimeUtc             datetime
AS
BEGIN
    DECLARE @DateActive datetime
    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)

    DECLARE @NumOnline int
    SELECT  @NumOnline = COUNT(*)
    FROM    dbo.aspnet_Users u(NOLOCK),
            dbo.aspnet_Applications a(NOLOCK),
            dbo.aspnet_Membership m(NOLOCK)
    WHERE   u.ApplicationId = a.ApplicationId                  AND
            LastActivityDate > @DateActive                     AND
            a.LoweredApplicationName = LOWER(@ApplicationName) AND
            u.UserId = m.UserId
    RETURN(@NumOnline)
END
~
CREATE PROCEDURE dbo.aspnet_Membership_GetPassword
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @MaxInvalidPasswordAttempts     int,
    @PasswordAttemptWindow          int,
    @CurrentTimeUtc                 datetime,
    @PasswordAnswer                 nvarchar(128) = NULL
AS
BEGIN
    DECLARE @UserId                                 uniqueidentifier
    DECLARE @PasswordFormat                         int
    DECLARE @Password                               nvarchar(128)
    DECLARE @passAns                                nvarchar(128)
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    SELECT  @UserId = u.UserId,
            @Password = m.Password,
            @passAns = m.PasswordAnswer,
            @PasswordFormat = m.PasswordFormat,
            @IsLockedOut = m.IsLockedOut,
            @LastLockoutDate = m.LastLockoutDate,
            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF ( @@rowcount = 0 )
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    IF( @IsLockedOut = 1 )
    BEGIN
        SET @ErrorCode = 99
        GOTO Cleanup
    END

    IF ( NOT( @PasswordAnswer IS NULL ) )
    BEGIN
        IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
        BEGIN
            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
            END

            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @CurrentTimeUtc
                END
            END

            SET @ErrorCode = 3
        END
        ELSE
        BEGIN
            IF( @FailedPasswordAnswerAttemptCount > 0 )
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = 0
                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            END
        END

        UPDATE dbo.aspnet_Membership
        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
        WHERE @UserId = UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    IF( @ErrorCode = 0 )
        SELECT @Password, @PasswordFormat

    RETURN @ErrorCode

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
~
CREATE PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @UpdateLastLoginActivityDate    bit,
    @CurrentTimeUtc                 datetime
AS
BEGIN
    DECLARE @IsLockedOut                        bit
    DECLARE @UserId                             uniqueidentifier
    DECLARE @Password                           nvarchar(128)
    DECLARE @PasswordSalt                       nvarchar(128)
    DECLARE @PasswordFormat                     int
    DECLARE @FailedPasswordAttemptCount         int
    DECLARE @FailedPasswordAnswerAttemptCount   int
    DECLARE @IsApproved                         bit
    DECLARE @LastActivityDate                   datetime
    DECLARE @LastLoginDate                      datetime

    SELECT  @UserId          = NULL

    SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
            @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
		    @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
            @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF (@UserId IS NULL)
        RETURN 1

    IF (@IsLockedOut = 1)
        RETURN 99

    SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
             @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate

    IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
    BEGIN
        UPDATE  dbo.aspnet_Membership
        SET     LastLoginDate = @CurrentTimeUtc
        WHERE   UserId = @UserId

        UPDATE  dbo.aspnet_Users
        SET     LastActivityDate = @CurrentTimeUtc
        WHERE   @UserId = UserId
    END


    RETURN 0
END
~
CREATE PROCEDURE dbo.aspnet_Membership_GetUserByEmail
    @ApplicationName  nvarchar(256),
    @Email            nvarchar(256)
AS
BEGIN
    IF( @Email IS NULL )
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                m.LoweredEmail IS NULL
    ELSE
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@Email) = m.LoweredEmail

    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END
~
CREATE PROCEDURE dbo.aspnet_Membership_GetUserByName
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier

    IF (@UpdateLastActivity = 1)
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, @CurrentTimeUtc, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1

        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        WHERE    @UserId = UserId
    END
    ELSE
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1
    END

    RETURN 0
END
~
CREATE PROCEDURE dbo.aspnet_Membership_GetUserByUserId
    @UserId               uniqueidentifier,
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    IF ( @UpdateLastActivity = 1 )
    BEGIN
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        FROM     dbo.aspnet_Users
        WHERE    @UserId = UserId

        IF ( @@ROWCOUNT = 0 ) -- User ID not found
            RETURN -1
    END

    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate, m.LastLoginDate, u.LastActivityDate,
            m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
            m.LastLockoutDate
    FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   @UserId = u.UserId AND u.UserId = m.UserId

    IF ( @@ROWCOUNT = 0 ) -- User ID not found
       RETURN -1

    RETURN 0
END
~
CREATE PROCEDURE dbo.aspnet_Membership_ResetPassword
    @ApplicationName             nvarchar(256),
    @UserName                    nvarchar(256),
    @NewPassword                 nvarchar(128),
    @MaxInvalidPasswordAttempts  int,
    @PasswordAttemptWindow       int,
    @PasswordSalt                nvarchar(128),
    @CurrentTimeUtc              datetime,
    @PasswordFormat              int = 0,
    @PasswordAnswer              nvarchar(128) = NULL
AS
BEGIN
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

    DECLARE @UserId                                 uniqueidentifier
    SET     @UserId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF ( @UserId IS NULL )
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    SELECT @IsLockedOut = IsLockedOut,
           @LastLockoutDate = LastLockoutDate,
           @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
           @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
           @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
           @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
    FROM dbo.aspnet_Membership WITH ( UPDLOCK )
    WHERE @UserId = UserId

    IF( @IsLockedOut = 1 )
    BEGIN
        SET @ErrorCode = 99
        GOTO Cleanup
    END

    UPDATE dbo.aspnet_Membership
    SET    Password = @NewPassword,
           LastPasswordChangedDate = @CurrentTimeUtc,
           PasswordFormat = @PasswordFormat,
           PasswordSalt = @PasswordSalt
    WHERE  @UserId = UserId AND
           ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )

    IF ( @@ROWCOUNT = 0 )
        BEGIN
            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
            END

            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @CurrentTimeUtc
                END
            END

            SET @ErrorCode = 3
        END
    ELSE
        BEGIN
            IF( @FailedPasswordAnswerAttemptCount > 0 )
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = 0
                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            END
        END

    IF( NOT ( @PasswordAnswer IS NULL ) )
    BEGIN
        UPDATE dbo.aspnet_Membership
        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
        WHERE @UserId = UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN @ErrorCode

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
~
CREATE PROCEDURE dbo.aspnet_Membership_SetPassword
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @NewPassword      nvarchar(128),
    @PasswordSalt     nvarchar(128),
    @CurrentTimeUtc   datetime,
    @PasswordFormat   int = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF (@UserId IS NULL)
        RETURN(1)

    UPDATE dbo.aspnet_Membership
    SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
        LastPasswordChangedDate = @CurrentTimeUtc
    WHERE @UserId = UserId
    RETURN(0)
END
~
CREATE PROCEDURE dbo.aspnet_Membership_UnlockUser
    @ApplicationName                         nvarchar(256),
    @UserName                                nvarchar(256)
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF ( @UserId IS NULL )
        RETURN 1

    UPDATE dbo.aspnet_Membership
    SET IsLockedOut = 0,
        FailedPasswordAttemptCount = 0,
        FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
        FailedPasswordAnswerAttemptCount = 0,
        FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
        LastLockoutDate = CONVERT( datetime, '17540101', 112 )
    WHERE @UserId = UserId

    RETURN 0
END
~
CREATE PROCEDURE dbo.aspnet_Membership_UpdateUser
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @Email                nvarchar(256),
    @Comment              ntext,
    @IsApproved           bit,
    @LastLoginDate        datetime,
    @LastActivityDate     datetime,
    @UniqueEmail          int,
    @CurrentTimeUtc       datetime
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId, @ApplicationId = a.ApplicationId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF (@UserId IS NULL)
        RETURN(1)

    IF (@UniqueEmail = 1)
    BEGIN
        IF (EXISTS (SELECT *
                    FROM  dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
                    WHERE ApplicationId = @ApplicationId  AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
        BEGIN
            RETURN(7)
        END
    END

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
	SET @TranStarted = 0

    UPDATE dbo.aspnet_Users WITH (ROWLOCK)
    SET
         LastActivityDate = @LastActivityDate
    WHERE
       @UserId = UserId

    IF( @@ERROR <> 0 )
        GOTO Cleanup

    UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
    SET
         Email            = @Email,
         LoweredEmail     = LOWER(@Email),
         Comment          = @Comment,
         IsApproved       = @IsApproved,
         LastLoginDate    = @LastLoginDate
    WHERE
       @UserId = UserId

    IF( @@ERROR <> 0 )
        GOTO Cleanup

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN -1
END
~
CREATE PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @IsPasswordCorrect              bit,
    @UpdateLastLoginActivityDate    bit,
    @MaxInvalidPasswordAttempts     int,
    @PasswordAttemptWindow          int,
    @CurrentTimeUtc                 datetime,
    @LastLoginDate                  datetime,
    @LastActivityDate               datetime
AS
BEGIN
    DECLARE @UserId                                 uniqueidentifier
    DECLARE @IsApproved                             bit
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    SELECT  @UserId = u.UserId,
            @IsApproved = m.IsApproved,
            @IsLockedOut = m.IsLockedOut,
            @LastLockoutDate = m.LastLockoutDate,
            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF ( @@rowcount = 0 )
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    IF( @IsLockedOut = 1 )
    BEGIN
        GOTO Cleanup
    END

    IF( @IsPasswordCorrect = 0 )
    BEGIN
        IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
            SET @FailedPasswordAttemptCount = 1
        END
        ELSE
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
            SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
        END

        BEGIN
            IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
            BEGIN
                SET @IsLockedOut = 1
                SET @LastLockoutDate = @CurrentTimeUtc
            END
        END
    END
    ELSE
    BEGIN
        IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
        BEGIN
            SET @FailedPasswordAttemptCount = 0
            SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            SET @FailedPasswordAnswerAttemptCount = 0
            SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
        END
    END

    IF( @UpdateLastLoginActivityDate = 1 )
    BEGIN
        UPDATE  dbo.aspnet_Users
        SET     LastActivityDate = @LastActivityDate
        WHERE   @UserId = UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END

        UPDATE  dbo.aspnet_Membership
        SET     LastLoginDate = @LastLoginDate
        WHERE   UserId = @UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END


    UPDATE dbo.aspnet_Membership
    SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
        FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
        FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
        FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
        FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
    WHERE @UserId = UserId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN @ErrorCode

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_DeleteAllState (
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @Count int OUT)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        IF (@AllUsersScope = 1)
            DELETE FROM aspnet_PersonalizationAllUsers
            WHERE PathId IN
               (SELECT Paths.PathId
                FROM dbo.aspnet_Paths Paths
                WHERE Paths.ApplicationId = @ApplicationId)
        ELSE
            DELETE FROM aspnet_PersonalizationPerUser
            WHERE PathId IN
               (SELECT Paths.PathId
                FROM dbo.aspnet_Paths Paths
                WHERE Paths.ApplicationId = @ApplicationId)

        SELECT @Count = @@ROWCOUNT
    END
END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_GetCountOfState (
    @Count int OUT,
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @Path NVARCHAR(256) = NULL,
    @UserName NVARCHAR(256) = NULL,
    @InactiveSinceDate DATETIME = NULL)
AS
BEGIN

    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
        IF (@AllUsersScope = 1)
            SELECT @Count = COUNT(*)
            FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
            WHERE Paths.ApplicationId = @ApplicationId
                  AND AllUsers.PathId = Paths.PathId
                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
        ELSE
            SELECT @Count = COUNT(*)
            FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
            WHERE Paths.ApplicationId = @ApplicationId
                  AND PerUser.UserId = Users.UserId
                  AND PerUser.PathId = Paths.PathId
                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
                  AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
                  AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_ResetSharedState (
    @Count int OUT,
    @ApplicationName NVARCHAR(256),
    @Path NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationAllUsers
        WHERE PathId IN
            (SELECT AllUsers.PathId
             FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
             WHERE Paths.ApplicationId = @ApplicationId
                   AND AllUsers.PathId = Paths.PathId
                   AND Paths.LoweredPath = LOWER(@Path))

        SELECT @Count = @@ROWCOUNT
    END
END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_ResetUserState (
    @Count                  int                 OUT,
    @ApplicationName        NVARCHAR(256),
    @InactiveSinceDate      DATETIME            = NULL,
    @UserName               NVARCHAR(256)       = NULL,
    @Path                   NVARCHAR(256)       = NULL)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationPerUser
        WHERE Id IN (SELECT PerUser.Id
                     FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
                     WHERE Paths.ApplicationId = @ApplicationId
                           AND PerUser.UserId = Users.UserId
                           AND PerUser.PathId = Paths.PathId
                           AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
                           AND (@UserName IS NULL OR Users.LoweredUserName = LOWER(@UserName))
                           AND (@Path IS NULL OR Paths.LoweredPath = LOWER(@Path)))

        SELECT @Count = @@ROWCOUNT
    END
END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationAllUsers_GetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @Path              NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationAllUsers p WHERE p.PathId = @PathId
END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationAllUsers_ResetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @Path              NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    DELETE FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId
    RETURN 0
END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationAllUsers_SetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @Path             NVARCHAR(256),
    @PageSettings     IMAGE,
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
    END

    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId))
        UPDATE dbo.aspnet_PersonalizationAllUsers SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE PathId = @PathId
    ELSE
        INSERT INTO dbo.aspnet_PersonalizationAllUsers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)
    RETURN 0
END
~

CREATE PROCEDURE dbo.aspnet_PersonalizationPerUser_GetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        RETURN
    END

    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId
END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationPerUser_ResetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        RETURN
    END

    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId
    RETURN 0
END
~
CREATE PROCEDURE dbo.aspnet_PersonalizationPerUser_SetPageSettings (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @PageSettings     IMAGE,
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
    END

    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
    END

    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationPerUser WHERE UserId = @UserId AND PathId = @PathId))
        UPDATE dbo.aspnet_PersonalizationPerUser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE UserId = @UserId AND PathId = @PathId
    ELSE
        INSERT INTO dbo.aspnet_PersonalizationPerUser(UserId, PathId, PageSettings, LastUpdatedDate) VALUES (@UserId, @PathId, @PageSettings, @CurrentTimeUtc)
    RETURN 0
END
~

CREATE PROCEDURE dbo.aspnet_Profile_DeleteInactiveProfiles
    @ApplicationName        nvarchar(256),
    @ProfileAuthOptions     int,
    @InactiveSinceDate      datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
    BEGIN
        SELECT  0
        RETURN
    END

    DELETE
    FROM    dbo.aspnet_Profile
    WHERE   UserId IN
            (   SELECT  UserId
                FROM    dbo.aspnet_Users u
                WHERE   ApplicationId = @ApplicationId
                        AND (LastActivityDate <= @InactiveSinceDate)
                        AND (
                                (@ProfileAuthOptions = 2)
                             OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                             OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                            )
            )

    SELECT  @@ROWCOUNT
END
~

CREATE PROCEDURE dbo.aspnet_Profile_GetNumberOfInactiveProfiles
    @ApplicationName        nvarchar(256),
    @ProfileAuthOptions     int,
    @InactiveSinceDate      datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
    BEGIN
        SELECT 0
        RETURN
    END

    SELECT  COUNT(*)
    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
    WHERE   ApplicationId = @ApplicationId
        AND u.UserId = p.UserId
        AND (LastActivityDate <= @InactiveSinceDate)
        AND (
                (@ProfileAuthOptions = 2)
                OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
            )
END
~

CREATE PROCEDURE dbo.aspnet_Profile_GetProperties
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

    IF (@UserId IS NULL)
        RETURN
    SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
    FROM         dbo.aspnet_Profile
    WHERE        UserId = @UserId

    IF (@@ROWCOUNT > 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate=@CurrentTimeUtc
        WHERE  UserId = @UserId
    END
END
~
CREATE PROCEDURE dbo.aspnet_Profile_SetProperties
    @ApplicationName        nvarchar(256),
    @PropertyNames          ntext,
    @PropertyValuesString   ntext,
    @PropertyValuesBinary   image,
    @UserName               nvarchar(256),
    @IsUserAnonymous        bit,
    @CurrentTimeUtc         datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
       BEGIN TRANSACTION
       SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    DECLARE @UserId uniqueidentifier
    DECLARE @LastActivityDate datetime
    SELECT  @UserId = NULL
    SELECT  @LastActivityDate = @CurrentTimeUtc

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    UPDATE dbo.aspnet_Users
    SET    LastActivityDate=@CurrentTimeUtc
    WHERE  UserId = @UserId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF (EXISTS( SELECT *
               FROM   dbo.aspnet_Profile
               WHERE  UserId = @UserId))
        UPDATE dbo.aspnet_Profile
        SET    PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
               PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc
        WHERE  UserId = @UserId
    ELSE
        INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
             VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
~

CREATE PROCEDURE dbo.aspnet_Roles_DeleteRole
    @ApplicationName            nvarchar(256),
    @RoleName                   nvarchar(256),
    @DeleteOnlyIfRoleIsEmpty    bit
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    DECLARE @RoleId   uniqueidentifier
    SELECT  @RoleId = NULL
    SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId

    IF (@RoleId IS NULL)
    BEGIN
        SELECT @ErrorCode = 1
        GOTO Cleanup
    END
    IF (@DeleteOnlyIfRoleIsEmpty <> 0)
    BEGIN
        IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId))
        BEGIN
            SELECT @ErrorCode = 2
            GOTO Cleanup
        END
    END


    DELETE FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId  AND ApplicationId = @ApplicationId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN(0)

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
END
~
CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
	@ApplicationName  nvarchar(256),
	@UserNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000),
	@CurrentTimeUtc   datetime
AS
BEGIN
	DECLARE @AppId uniqueidentifier
	SELECT  @AppId = NULL
	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@AppId IS NULL)
		RETURN(2)
	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num		int
	DECLARE @Pos		int
	DECLARE @NextPos	int
	DECLARE @Name		nvarchar(256)

	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbRoles
	  SELECT RoleId
	  FROM   dbo.aspnet_Roles ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		SELECT TOP 1 Name
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(2)
	END

	DELETE FROM @tbNames WHERE 1=1
	SET @Num = 0
	SET @Pos = 1

	WHILE(@Pos <= LEN(@UserNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @UserNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@UserNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbUsers
	  SELECT UserId
	  FROM   dbo.aspnet_Users ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		DELETE FROM @tbNames
		WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)

		INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
		  SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
		  FROM   @tbNames

		INSERT INTO @tbUsers
		  SELECT  UserId
		  FROM	dbo.aspnet_Users au, @tbNames t
		  WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
	END

	IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
	BEGIN
		SELECT TOP 1 UserName, RoleName
		FROM		 dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
		WHERE		u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId

		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(3)
	END

	INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
	SELECT UserId, RoleId
	FROM @tbUsers, @tbRoles

	IF( @TranStarted = 1 )
		COMMIT TRANSACTION
	RETURN(0)
END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
~


CREATE PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256),
    @UserNameToMatch  nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
     DECLARE @RoleId uniqueidentifier
     SELECT  @RoleId = NULL

     SELECT  @RoleId = RoleId
     FROM    dbo.aspnet_Roles
     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

     IF (@RoleId IS NULL)
         RETURN(1)

    SELECT u.UserName
    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
    ORDER BY u.UserName
    RETURN(0)
END
~
CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL

    SELECT  @UserId = UserId
    FROM    dbo.aspnet_Users
    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId

    IF (@UserId IS NULL)
        RETURN(1)

    SELECT r.RoleName
    FROM   dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
    WHERE  r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
    ORDER BY r.RoleName
    RETURN (0)
END
~

CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
     DECLARE @RoleId uniqueidentifier
     SELECT  @RoleId = NULL

     SELECT  @RoleId = RoleId
     FROM    dbo.aspnet_Roles
     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

     IF (@RoleId IS NULL)
         RETURN(1)

    SELECT u.UserName
    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
    ORDER BY u.UserName
    RETURN(0)
END
~

CREATE PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(2)
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    DECLARE @RoleId uniqueidentifier
    SELECT  @RoleId = NULL

    SELECT  @UserId = UserId
    FROM    dbo.aspnet_Users
    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId

    IF (@UserId IS NULL)
        RETURN(2)

    SELECT  @RoleId = RoleId
    FROM    dbo.aspnet_Roles
    WHERE   LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId

    IF (@RoleId IS NULL)
        RETURN(3)

    IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE  UserId = @UserId AND RoleId = @RoleId))
        RETURN(1)
    ELSE
        RETURN(0)
END
~

CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
	@ApplicationName  nvarchar(256),
	@UserNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000)
AS
BEGIN
	DECLARE @AppId uniqueidentifier
	SELECT  @AppId = NULL
	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@AppId IS NULL)
		RETURN(2)


	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @tbNames  table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles  table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbUsers  table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num	  int
	DECLARE @Pos	  int
	DECLARE @NextPos  int
	DECLARE @Name	  nvarchar(256)
	DECLARE @CountAll int
	DECLARE @CountU	  int
	DECLARE @CountR	  int


	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbRoles
	  SELECT RoleId
	  FROM   dbo.aspnet_Roles ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
	SELECT @CountR = @@ROWCOUNT

	IF (@CountR <> @Num)
	BEGIN
		SELECT TOP 1 N'', Name
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(2)
	END


	DELETE FROM @tbNames WHERE 1=1
	SET @Num = 0
	SET @Pos = 1


	WHILE(@Pos <= LEN(@UserNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @UserNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@UserNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbUsers
	  SELECT UserId
	  FROM   dbo.aspnet_Users ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

	SELECT @CountU = @@ROWCOUNT
	IF (@CountU <> @Num)
	BEGIN
		SELECT TOP 1 Name, N''
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE u.UserId = au.UserId)

		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(1)
	END

	SELECT  @CountAll = COUNT(*)
	FROM	dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
	WHERE   ur.UserId = u.UserId AND ur.RoleId = r.RoleId

	IF (@CountAll <> @CountU * @CountR)
	BEGIN
		SELECT TOP 1 UserName, RoleName
		FROM		 @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
		WHERE		 u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
					 tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
					 tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(3)
	END

	DELETE FROM dbo.aspnet_UsersInRoles
	WHERE UserId IN (SELECT UserId FROM @tbUsers)
	  AND RoleId IN (SELECT RoleId FROM @tbRoles)
	IF( @TranStarted = 1 )
		COMMIT TRANSACTION
	RETURN(0)
END
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
~
CREATE PROCEDURE [dbo].aspnet_Users_DeleteUser
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @TablesToDeleteFrom int,
    @NumTablesDeletedFrom int OUTPUT
AS
BEGIN
    DECLARE @UserId               uniqueidentifier
    SELECT  @UserId               = NULL
    SELECT  @NumTablesDeletedFrom = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
	SET @TranStarted = 0

    DECLARE @ErrorCode   int
    DECLARE @RowCount    int

    SET @ErrorCode = 0
    SET @RowCount  = 0

    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a
    WHERE   u.LoweredUserName       = LOWER(@UserName)
        AND u.ApplicationId         = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@UserId IS NULL)
    BEGIN
        GOTO Cleanup
    END

    -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
    BEGIN
        DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId

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

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

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

    -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
    IF ((@TablesToDeleteFrom & 2) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_UsersInRoles') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId

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

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

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

    -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
    IF ((@TablesToDeleteFrom & 4) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId

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

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

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

    -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
    IF ((@TablesToDeleteFrom & 8) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId

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

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

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

    -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (@TablesToDeleteFrom & 2) <> 0 AND
        (@TablesToDeleteFrom & 4) <> 0 AND
        (@TablesToDeleteFrom & 8) <> 0 AND
        (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
    BEGIN
        DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId

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

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

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

    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:
    SET @NumTablesDeletedFrom = 0

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
	    ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
~

CREATE PROCEDURE dbo.aspnet_Profile_DeleteProfiles
    @ApplicationName        nvarchar(256),
    @UserNames              nvarchar(4000)
AS
BEGIN
    DECLARE @UserName     nvarchar(256)
    DECLARE @CurrentPos   int
    DECLARE @NextPos      int
    DECLARE @NumDeleted   int
    DECLARE @DeletedUser  int
    DECLARE @TranStarted  bit
    DECLARE @ErrorCode    int

    SET @ErrorCode = 0
    SET @CurrentPos = 1
    SET @NumDeleted = 0
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    WHILE (@CurrentPos <= LEN(@UserNames))
    BEGIN
        SELECT @NextPos = CHARINDEX(N',', @UserNames,  @CurrentPos)
        IF (@NextPos = 0 OR @NextPos IS NULL)
            SELECT @NextPos = LEN(@UserNames) + 1

        SELECT @UserName = SUBSTRING(@UserNames, @CurrentPos, @NextPos - @CurrentPos)
        SELECT @CurrentPos = @NextPos+1

        IF (LEN(@UserName) > 0)
        BEGIN
            SELECT @DeletedUser = 0
            EXEC dbo.aspnet_Users_DeleteUser @ApplicationName, @UserName, 4, @DeletedUser OUTPUT
            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
            IF (@DeletedUser <> 0)
                SELECT @NumDeleted = @NumDeleted + 1
        END
    END
    SELECT @NumDeleted
    IF (@TranStarted = 1)
    BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
    END
    SET @TranStarted = 0

    RETURN 0

Cleanup:
    IF (@TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    RETURN @ErrorCode
END
~

CREATE TRIGGER YX_DePic on YX_Building_Household 
FOR INSERT
AS
BEGIN 
UPDATE YX_Building_Household SET Hou_Isdefault =1 WHERE Hou_ID in 
(SELECT MIN(hou_ID) AS MinID FROM YX_Building_Household where hou_type=1 GROUP BY bu_id HAVING COUNT(bu_id)>0 )
END

~
CREATE TRIGGER YX_DePic2 on YX_Building_Household 
FOR INSERT
AS
BEGIN 
UPDATE YX_Building_Household SET Hou_Isdefault =0 WHERE Hou_ID not  in 
(SELECT MIN(hou_ID) AS MinID FROM YX_Building_Household GROUP BY bu_id HAVING COUNT(bu_id)>0)
END

~
create TRIGGER yx_weizhi on YX_Building_Household 
FOR INSERT
AS
BEGIN 
UPDATE YX_Building_Household SET Hou_Isdefault =1 WHERE Hou_ID in 
(SELECT MIN(hou_ID) AS MinID FROM YX_Building_Household where hou_type=2 GROUP BY bu_id HAVING COUNT(bu_id)>0 )
END

~
create TRIGGER yx_huxing on YX_Building_Household 
FOR INSERT
AS
BEGIN 
UPDATE YX_Building_Household SET Hou_Isdefault =1 WHERE Hou_ID in 
(SELECT MIN(hou_ID) AS MinID FROM YX_Building_Household where hou_type=3 GROUP BY bu_id HAVING COUNT(bu_id)>0 )
END

~
create TRIGGER yx_zhanshi on YX_Building_Household 
FOR INSERT
AS
BEGIN 
UPDATE YX_Building_Household SET Hou_Isdefault =1 WHERE Hou_ID in 
(SELECT MIN(hou_ID) AS MinID FROM YX_Building_Household where hou_type=4 GROUP BY bu_id HAVING COUNT(bu_id)>0 )
END


~CREATE TRIGGER YX_PICTURE ON  YX_CHUSHOU 
FOR INSERT
AS
UPDATE YX_CHUSHOU SET House_IsHavePicture=1
WHERE HOUSE_BIANHAO IN (SELECT Distinct TP_BIANHAO FROM YX_TUPIAN)

~
CREATE TRIGGER YX_DefaultPic on YX_TuPian 
FOR INSERT
AS
BEGIN 
UPDATE YX_TuPian SET TP_IsDefault =1 WHERE TP_ID IN
(SELECT MIN(TP_ID) AS MinID FROM YX_TuPian GROUP BY TP_BianHao HAVING COUNT(TP_BianHao)>0)
END

~
CREATE TRIGGER YX_NotDefaultPic on YX_TuPian 
FOR INSERT
AS
BEGIN 
UPDATE YX_TuPian SET TP_IsDefault =0 WHERE TP_ID NOT IN
(SELECT MIN(TP_ID) AS MinID FROM YX_TuPian GROUP BY TP_BianHao HAVING COUNT(TP_BianHao)>0)
END

