usp_InsertUpdateSiteAccount.sql
3.92 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertUpdateSiteAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_InsertUpdateSiteAccount]
GO
-- ====================================================
-- Author: Magic Software
-- Create date: 08-Feb-2018
-- Description: To insert or update a site account for a license account and site
-- ====================================================
create PROCEDURE [dbo].[usp_InsertUpdateSiteAccount]
-- Add the parameters for the stored procedure here
@iSiteId int, @sSiteIP varchar(2000), @sTitle varchar(100), @sInstituteName varchar(100), @sDepartment varchar(50),
@sAddress1 varchar(100), @sAddress2 varchar(100), @sCity varchar(50), @Zip varchar(20), @Phone varchar(30),
@StateId int, @CountryId int, @IsMaster bit, @CreationDate datetime, @ModifiedDate datetime,
@IsActive bit, @UserId int, @sSiteIPTo varchar(100), @LicenseId int, @SiteEditionIds varchar(1000),
@Status bit out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @list varchar(1000)
DECLARE @pos INT, @tempEditionId int, @tempLicenseEditionId int;
DECLARE @len INT, @tempModesty bit;
DECLARE @value varchar(1000)
CREATE TABLE #LocalTempTable(
SiteId int,
LicenseEditionId int,
IsModesty bit);
if(@SiteEditionIds != '')
begin
set @SiteEditionIds = @SiteEditionIds + ',';
end
SET @list = @SiteEditionIds
set @Status = 0;
BEGIN TRY
BEGIN TRANSACTION
IF @iSiteId = 0
BEGIN
INSERT INTO [dbo].[Site]([SiteIP],[Title],[InstituteName],[Department],[Address1],[Address2],
[City],[Zip],[Phone],[StateId],[CountryId],[IsMaster],[CreationDate],[ModifiedDate],[IsActive],[SiteIPTo])
VALUES(@sSiteIP, @sTitle, @sInstituteName, @sDepartment, @sAddress1, @sAddress2, @sCity, @Zip, @Phone,
@StateId, @CountryId, @IsMaster, @CreationDate, @ModifiedDate, @IsActive, @sSiteIPTo);
-- to get the last inserted identity value in the current session
SET @iSiteId=SCOPE_IDENTITY();
insert into AIAUserToSite values(@UserId, @iSiteId);
END
ELSE
BEGIN
UPDATE [dbo].[Site] SET [SiteIP]=@sSiteIP, [Title]=@sTitle,[InstituteName]=@sInstituteName,
[Department]=@sDepartment, [Address1]=@sAddress1, [Address2]=@sAddress2,[City]=@sCity,
[Zip]=@Zip, [Phone]=@Phone, [StateId]=@StateId, [CountryId]=@CountryId,
[ModifiedDate]=@ModifiedDate, [IsActive]=@IsActive, [SiteIPTo]=@sSiteIPTo
WHERE [Id]=@iSiteId
END
insert into #LocalTempTable
select SLE.* from SiteToLicenseEdition SLE inner join LicenseToEdition LE on SLE.LicenseEditionId = LE.Id where
SLE.SiteId = @iSiteId and LicenseId = @LicenseId;
delete SLE from SiteToLicenseEdition SLE inner join LicenseToEdition LE on SLE.LicenseEditionId = LE.Id where
SLE.SiteId = @iSiteId and LicenseId = @LicenseId;
set @pos = 0
set @len = 0
WHILE CHARINDEX(',', @list, @pos+1)>0
BEGIN
set @len = CHARINDEX(',', @list, @pos+1) - @pos;
set @value = SUBSTRING(@list, @pos, @len);
set @tempEditionId = convert(int, @value);
select @tempLicenseEditionId = Id from LicenseToEdition where LicenseId = @LicenseId and EditionId = @tempEditionId;
set @tempModesty = 0;
if(exists(select * from #LocalTempTable where LicenseEditionId = @tempLicenseEditionId and SiteId = @iSiteId))
begin
select @tempModesty = IsModesty from #LocalTempTable where LicenseEditionId = @tempLicenseEditionId and SiteId = @iSiteId;
end
insert into SiteToLicenseEdition(SiteId, LicenseEditionId, IsModesty) values(@iSiteId, @tempLicenseEditionId, @tempModesty);
set @pos = CHARINDEX(',', @list, @pos+@len) + 1;
END
COMMIT TRANSACTION
set @Status = 1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO