CREATE Procedure [dbo].[spUserInsert] -- 'sahil1233','sahil122.enest@gmail.com','','lakhwant','singh','#57,urban estate','ludhiana','punjab','india','144001',12323,'1234567',0,1,1,1,1,1,0,1,1
(
@UserId int,
@UserName nvarchar(50),
@EmailAddress nvarchar(50),
@CompanyName nvarchar(50),
@FirstName nvarchar(50),
@Surname nvarchar(50),
@Address nvarchar(250),
@City nvarchar(50),
@States nvarchar(50),
@Country nvarchar(50),
@Zipcode nvarchar(50),
@Phone nvarchar(50),
@Password nvarchar(50),
@IsAccountVerified int,
@IsUserInvited int
)
AS
BEGIN
/*=============================================================================
* Constants
*============================================================================*/
declare
@SUCCESS smallint,
@FAILED smallint,
@ERROR_SEVERITY smallint,
@ERROR_STATE1 smallint,
@theErrorMsg nvarchar(4000),
@theErrorState int,
@CompanyId int,
@RetGuid uniqueidentifier = NEWID()
select
@SUCCESS = 0,
@FAILED = -1,
@ERROR_SEVERITY = 11,
@ERROR_STATE1 = 1
Begin Try
begin transaction
if(@IsUserinvited<>1)
Begin
if not exists(select 1 from dbo.MT_Company where companyName=@companyName)
begin
if not exists(select 1 from MT_Users where UserName=@UserName)
begin
Insert into dbo.MT_Company (CompanyName,CreatedBy,CreatedDate,IsCompanyEnabled,EnforceMileageRate)
values(@CompanyName,@UserName,GETDATE(),1,1)
set @CompanyId=@@IDENTITY
Insert into dbo.MT_Users (UserName,EmailAddress,FirstName,Surname,[Address],City,States,
Country,ZipCode,Phone,[Password],[IsAccountVerified],[MileageCalculationAllow],IsDeleted,CreateBy,CreateDate,UserGuid,companyID,IsCompanyAdmin)values
(@UserName,@EmailAddress,@FirstName,@Surname,@Address,@City,@States,@Country,
@Zipcode,@Phone,@Password,@IsAccountVerified,1,0,@UserName,GETDATE(),@RetGuid,@CompanyId,1)
set @UserId=@@IDENTITY
Insert into dbo.MT_Transaction (CompanyId,UserId,EnableUser,[CurrentMonth],CurrentYear)values
(@CompanyId,@UserId,1,MONTH(getdate()),Year(getdate()))
SELECT Convert(nvarchar(max),@RetGuid) As InsertedID
end
else
begin
select Convert(nvarchar(50),0) as InsertedID
end
end
else
begin
select Convert(nvarchar(50),1) as InsertedID
end
End
else
begin
declare @InvitedCompanyId int
select @InvitedCompanyId=CompanyId from dbo.MT_Company where CompanyName=@CompanyName
if not exists(select 1 from MT_Users where UserName=@UserName)
begin
Insert into dbo.MT_Users (UserName,EmailAddress,FirstName,Surname,[Address],City,States,
Country,ZipCode,Phone,[Password],[IsAccountVerified],[MileageCalculationAllow],IsDeleted,CreateBy,
CreateDate,UserGuid,companyID,IsCompanyAdmin)values
(@UserName,@EmailAddress,@FirstName,@Surname,@Address,@City,@States,@Country,
@Zipcode,@Phone,@Password,1,1,0,@UserName,GETDATE(),@RetGuid,@InvitedCompanyId,0)
SELECT Convert(nvarchar(max),@RetGuid) As InsertedID
end
else
begin
select Convert(nvarchar(50),0) as InsertedID
end
end
commit transaction
End Try
begin catch
if @@trancount > 0
rollback transaction
set @theErrorMsg = error_message()
set @theErrorState = error_state()
raiserror (@theErrorMsg, @ERROR_SEVERITY, @theErrorState)
return (@FAILED)
end catch
END
