dbo.InsertUpdateSiteAccount.StoredProcedure.sql
5.46 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
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[InsertUpdateSiteAccount] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sachin Verma
-- Create date: 28-Apr-2009
-- Description: To create or update a new site account
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertUpdateSiteAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertUpdateSiteAccount]
GO
CREATE PROCEDURE [dbo].[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, @IsModesty bit, @UserId int, @sSiteIPTo varchar(100)
AS
BEGIN
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
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()
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
-- Delete Old record from AIAUserToSite.
DELETE FROM AIAUserToSite Where SiteId=@iSiteId
-- Insert New record record from AIAUserToSite.
INSERT INTO [dbo].[AIAUserToSite] ([UserId],[SiteId])
VALUES(@UserId,@iSiteId)
COMMIT TRANSACTION
SELECT CONVERT( varchar(4000), @iSiteId) as Message1, @iSiteId as Message2
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT Error_Message() as Message1, Error_Line() as Message2
END CATCH
END
GO