Hello Everyone,
I just started a developing a new application for hospitals (for maintaining hospital staff, patient, appointments etc.). I started with the database part first.
I will use APIs as back-end. So I just thought why not I should take all data in one go in JSON and try to insert it.
So here I have wrote tables and procedures which are working perfectly without any issue / problem.
Here I need to ask you respected members is this a right approach? OR there may be any better approach to achieve this.
For reference I am sharing my tables and procedures.
I request you kindly suggest in case I need to take care of any other thing related to insert, because most of the time I am going to use this approach in my application.
USE [HMS];
GO
/****** Object: Table [App].[Hospitals] Script Date: 05-02-2022 08:37:21 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [App].[Hospitals]
([Id] [BIGINT] IDENTITY(1, 1) NOT NULL,
[HospitalName] [NVARCHAR](500) NOT NULL,
[Phone] [NVARCHAR](15) NOT NULL,
[Email] [NVARCHAR](50) NOT NULL,
[Fax] [NVARCHAR](50) NULL,
[Website] [NVARCHAR](50) NULL,
[GSTNo] [NVARCHAR](50) NOT NULL,
[PAN] [NVARCHAR](10) NULL,
[TIN] [NVARCHAR](50) NULL,
[AddressLine] [NVARCHAR](500) NOT NULL,
[CountryId] [SMALLINT] NOT NULL,
[StateId] [INT] NOT NULL,
[DistrictId] [INT] NOT NULL,
[CityId] [INT] NOT NULL,
[Pincode] [INT] NOT NULL,
[ContactPersonName] [NVARCHAR](250) NOT NULL,
[ContactPersonMobile] [NVARCHAR](15) NOT NULL,
[ContactPersonEmail] [NVARCHAR](50) NOT NULL,
[CreatedDate] [DATETIME] NOT NULL,
[CreatedBy] [BIGINT] NOT NULL,
[ModifiedDate] [DATETIME] NULL,
[ModifiedBy] [BIGINT] NULL,
[IsActive] [BIT] NOT NULL,
[HospitalCode] [NVARCHAR](100) NOT NULL,
CONSTRAINT [PK_Hospitals] PRIMARY KEY CLUSTERED([Id] ASC)
WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY];
GO
ALTER TABLE [App].[Hospitals]
ADD DEFAULT(GETDATE()) FOR [CreatedDate];
GO
ALTER TABLE [App].[Hospitals]
WITH CHECK
ADD CONSTRAINT [FK_Hospitals_Cities] FOREIGN KEY([CityId]) REFERENCES [Masters].[Cities]([Id]);
GO
ALTER TABLE [App].[Hospitals] CHECK CONSTRAINT [FK_Hospitals_Cities];
GO
ALTER TABLE [App].[Hospitals]
WITH CHECK
ADD CONSTRAINT [FK_Hospitals_Countries] FOREIGN KEY([CountryId]) REFERENCES [Masters].[Countries]([Id]);
GO
ALTER TABLE [App].[Hospitals] CHECK CONSTRAINT [FK_Hospitals_Countries];
GO
ALTER TABLE [App].[Hospitals]
WITH CHECK
ADD CONSTRAINT [FK_Hospitals_CreatedBy] FOREIGN KEY([CreatedBy]) REFERENCES [App].[Users]([Id]);
GO
ALTER TABLE [App].[Hospitals] CHECK CONSTRAINT [FK_Hospitals_CreatedBy];
GO
ALTER TABLE [App].[Hospitals]
WITH CHECK
ADD CONSTRAINT [FK_Hospitals_Districts] FOREIGN KEY([DistrictId]) REFERENCES [Masters].[Districts]([Id]);
GO
ALTER TABLE [App].[Hospitals] CHECK CONSTRAINT [FK_Hospitals_Districts];
GO
ALTER TABLE [App].[Hospitals]
WITH CHECK
ADD CONSTRAINT [FK_Hospitals_ModifiedBy] FOREIGN KEY([ModifiedBy]) REFERENCES [App].[Users]([Id]);
GO
ALTER TABLE [App].[Hospitals] CHECK CONSTRAINT [FK_Hospitals_ModifiedBy];
GO
ALTER TABLE [App].[Hospitals]
WITH CHECK
ADD CONSTRAINT [FK_Hospitals_States] FOREIGN KEY([StateId]) REFERENCES [Masters].[States]([Id]);
GO
ALTER TABLE [App].[Hospitals] CHECK CONSTRAINT [FK_Hospitals_States];
GO
USE [HMS];
GO
/****** Object: Table [App].[HospitalsLicenses] Script Date: 05-02-2022 08:37:58 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [App].[HospitalsLicenses]
([Id] [BIGINT] IDENTITY(1, 1) NOT NULL,
[HospitalId] [BIGINT] NOT NULL,
[LicenseStartDate] [DATETIME] NOT NULL,
[LicenseExpireDate] [DATETIME] NOT NULL,
[AssignedDate] [DATETIME] NOT NULL,
[AssignedBy] [BIGINT] NOT NULL,
[IsActive] [BIT] NOT NULL,
CONSTRAINT [PK_HospitalLicenses] PRIMARY KEY CLUSTERED([Id] ASC)
WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY];
GO
ALTER TABLE [App].[HospitalsLicenses]
ADD DEFAULT(GETDATE()) FOR [AssignedDate];
GO
ALTER TABLE [App].[HospitalsLicenses]
WITH CHECK
ADD CONSTRAINT [FK_HospitalsLicenses_AssignedBy] FOREIGN KEY([AssignedBy]) REFERENCES [App].[Users]([Id]);
GO
ALTER TABLE [App].[HospitalsLicenses] CHECK CONSTRAINT [FK_HospitalsLicenses_AssignedBy];
GO
ALTER TABLE [App].[HospitalsLicenses]
WITH CHECK
ADD CONSTRAINT [FK_HospitalsLicenses_Hospitals] FOREIGN KEY([HospitalId]) REFERENCES [App].[Hospitals]([Id]);
GO
ALTER TABLE [App].[HospitalsLicenses] CHECK CONSTRAINT [FK_HospitalsLicenses_Hospitals];
GO
USE [HMS];
GO
/****** Object: Table [App].[Users] Script Date: 05-02-2022 08:38:11 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [App].[Users]
([Id] [BIGINT] IDENTITY(1, 1) NOT NULL,
[FullName] [NVARCHAR](200) NOT NULL,
[UserName] [NVARCHAR](100) NOT NULL,
[Email] [NVARCHAR](100) NOT NULL,
[Phone] [NVARCHAR](15) NOT NULL,
[PasswordHash] [NVARCHAR](1000) NOT NULL,
[PasswordSalt] [NVARCHAR](1000) NOT NULL,
[UserTypeId] [TINYINT] NOT NULL,
[CreatedDate] [DATETIME] NOT NULL,
[CreatedBy] [BIGINT] NOT NULL,
[ModifiedDate] [DATETIME] NULL,
[ModifiedBy] [BIGINT] NULL,
[HospitalId] [BIGINT] NOT NULL,
[AddressLine] [NVARCHAR](500) NOT NULL,
[CityId] [INT] NOT NULL,
[DistrictId] [INT] NOT NULL,
[StateId] [INT] NOT NULL,
[CountryId] [SMALLINT] NOT NULL,
[Pincode] [INT] NOT NULL,
[IsActive] [BIT] NOT NULL,
[FailedAttempts] [TINYINT] NULL,
[LastLoggedIn] [DATETIME] NULL,
[IsLocked] [BIT] NULL,
[IpAddress] [NVARCHAR](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED([Id] ASC)
WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY];
GO
ALTER TABLE [App].[Users]
ADD DEFAULT(GETDATE()) FOR [CreatedDate];
GO
ALTER TABLE [App].[Users]
ADD DEFAULT((0)) FOR [IsLocked];
GO
ALTER TABLE [App].[Users]
WITH CHECK
ADD CONSTRAINT [FK_User_Hospitals] FOREIGN KEY([HospitalId]) REFERENCES [App].[Hospitals]([Id]);
GO
ALTER TABLE [App].[Users] CHECK CONSTRAINT [FK_User_Hospitals];
GO
ALTER TABLE [App].[Users]
WITH CHECK
ADD CONSTRAINT [FK_Users_Cities] FOREIGN KEY([CityId]) REFERENCES [Masters].[Cities]([Id]);
GO
ALTER TABLE [App].[Users] CHECK CONSTRAINT [FK_Users_Cities];
GO
ALTER TABLE [App].[Users]
WITH CHECK
ADD CONSTRAINT [FK_Users_Countries] FOREIGN KEY([CountryId]) REFERENCES [Masters].[Countries]([Id]);
GO
ALTER TABLE [App].[Users] CHECK CONSTRAINT [FK_Users_Countries];
GO
ALTER TABLE [App].[Users]
WITH CHECK
ADD CONSTRAINT [FK_Users_CreatedBy] FOREIGN KEY([CreatedBy]) REFERENCES [App].[Users]([Id]);
GO
ALTER TABLE [App].[Users] CHECK CONSTRAINT [FK_Users_CreatedBy];
GO
ALTER TABLE [App].[Users]
WITH CHECK
ADD CONSTRAINT [FK_Users_Districts] FOREIGN KEY([DistrictId]) REFERENCES [Masters].[Districts]([Id]);
GO
ALTER TABLE [App].[Users] CHECK CONSTRAINT [FK_Users_Districts];
GO
ALTER TABLE [App].[Users]
WITH CHECK
ADD CONSTRAINT [FK_Users_ModifiedBy] FOREIGN KEY([ModifiedBy]) REFERENCES [App].[Users]([Id]);
GO
ALTER TABLE [App].[Users] CHECK CONSTRAINT [FK_Users_ModifiedBy];
GO
ALTER TABLE [App].[Users]
WITH CHECK
ADD CONSTRAINT [FK_Users_States] FOREIGN KEY([StateId]) REFERENCES [Masters].[States]([Id]);
GO
ALTER TABLE [App].[Users] CHECK CONSTRAINT [FK_Users_States];
GO
USE [HMS];
GO
/****** Object: Table [App].[ResetPasswordRequests] Script Date: 05-02-2022 08:38:40 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [App].[ResetPasswordRequests]
([Id] [UNIQUEIDENTIFIER] NOT NULL,
[UserId] [BIGINT] NOT NULL,
[CreatedDate] [DATETIME] NOT NULL,
[CreatedBy] [BIGINT] NOT NULL,
[ModifiedDate] [DATETIME] NULL,
[ModifiedBy] [BIGINT] NULL,
[ExpireDate] [DATETIME] NOT NULL,
[IsReset] [BIT] NOT NULL,
[IsLoginFirstTime] [BIT] NOT NULL,
CONSTRAINT [PK_ResetPasswordRequests] PRIMARY KEY CLUSTERED([Id] ASC)
WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY];
GO
ALTER TABLE [App].[ResetPasswordRequests]
ADD CONSTRAINT [DF_ResetPasswordRequests_Id] DEFAULT(NEWID()) FOR [Id];
GO
ALTER TABLE [App].[ResetPasswordRequests]
ADD DEFAULT(GETDATE()) FOR [CreatedDate];
GO
ALTER TABLE [App].[ResetPasswordRequests]
ADD DEFAULT((0)) FOR [IsLoginFirstTime];
GO
ALTER TABLE [App].[ResetPasswordRequests]
WITH CHECK
ADD CONSTRAINT [FK_ResetPasswordRequests_CreatedBy] FOREIGN KEY([CreatedBy]) REFERENCES [App].[Users]([Id]);
GO
ALTER TABLE [App].[ResetPasswordRequests] CHECK CONSTRAINT [FK_ResetPasswordRequests_CreatedBy];
GO
ALTER TABLE [App].[ResetPasswordRequests]
WITH CHECK
ADD CONSTRAINT [FK_ResetPasswordRequests_ModifiedBy] FOREIGN KEY([ModifiedBy]) REFERENCES [App].[Users]([Id]);
GO
ALTER TABLE [App].[ResetPasswordRequests] CHECK CONSTRAINT [FK_ResetPasswordRequests_ModifiedBy];
GO
ALTER TABLE [App].[ResetPasswordRequests]
WITH CHECK
ADD CONSTRAINT [FK_ResetPasswordRequests_Users] FOREIGN KEY([UserId]) REFERENCES [App].[Users]([Id]);
GO
ALTER TABLE [App].[ResetPasswordRequests] CHECK CONSTRAINT [FK_ResetPasswordRequests_Users];
GO
USE [HMS];
GO
/****** Object: StoredProcedure [App].[usp_v1_add_hospital_details] Script Date: 05-02-2022 06:43:24 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author: <Bipin Kumar>
-- Create date: <07-06-2021>
-- Description: <To add the refresh token details>
-- =============================================
CREATE PROCEDURE [App].[usp_v1_add_hospital_details] @inputjson NVARCHAR(3000),
@createdby BIGINT,
@response INT OUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
--ADDING HOSPITAL
IF OBJECT_ID(N'tempdb..#T_Hospital') IS NOT NULL
BEGIN
DROP TABLE #T_Hospital;
END;
SELECT HospitalName,
Phone,
Email,
Fax,
Website,
GSTNo,
PAN,
TIN,
AddressLine,
CountryId,
StateId,
DistrictId,
CityId,
Pincode,
ContactPersonName,
ContactPersonMobile,
ContactPersonEmail,
GETDATE() AS CreatedDate,
@createdby AS CreatedBy,
NULL AS ModifiedDate,
NULL AS ModifiedBy,
1 AS IsActive,
HospitalCode
INTO #T_Hospital
FROM OPENJSON(@inputjson) WITH(HospitalName NVARCHAR(500) '$.Hospital.HospitalName', HospitalCode NVARCHAR(100) '$.Hospital.HospitalCode', Phone NVARCHAR(15) '$.Hospital.Phone', Email NVARCHAR(50) '$.Hospital.Email', Fax NVARCHAR(50) '$.Hospital.Fax', Website NVARCHAR(50) '$.Hospital.Website', GSTNo NVARCHAR(50) '$.Hospital.GSTNo', PAN NVARCHAR(10) '$.Hospital.PAN', TIN NVARCHAR(50) '$.Hospital.TIN', AddressLine NVARCHAR(500) '$.Hospital.AddressLine', CountryId SMALLINT '$.Hospital.CountryId', StateId INT '$.Hospital.StateId', DistrictId INT '$.Hospital.DistrictId', CityId INT '$.Hospital.CityId', Pincode INT '$.Hospital.Pincode', ContactPersonName NVARCHAR(250) '$.Hospital.ContactPersonName', ContactPersonMobile NVARCHAR(15) '$.Hospital.ContactPersonMobile', ContactPersonEmail NVARCHAR(50) '$.Hospital.ContactPersonEmail');
DECLARE @hospitalgstno NVARCHAR(50);
SELECT @hospitalgstno = GSTNo
FROM #T_Hospital;
IF NOT EXISTS
(
SELECT Id
FROM [App].[Hospitals]
WHERE GSTNo = @hospitalgstno
)
BEGIN
INSERT INTO [App].[Hospitals]
SELECT *
FROM #T_Hospital;
DECLARE @hospitalid BIGINT= SCOPE_IDENTITY();
DROP TABLE #T_Hospital;
--HOSPITAL LICENSE
IF OBJECT_ID(N'tempdb..#T_HospitalLicense') IS NOT NULL
BEGIN
DROP TABLE #T_HospitalLicense;
END;
DECLARE @startdate DATETIME;
DECLARE @expiredate DATETIME;
DECLARE @licenseperioddays INT;
SELECT LicensePeriodDays
INTO #T_HospitalLicense
FROM OPENJSON(@inputjson) WITH(LicensePeriodDays NVARCHAR(500) '$.HospitalLicense.LicensePeriodDays');
SELECT @licenseperioddays = LicensePeriodDays
FROM #T_HospitalLicense;
SET @startdate = GETDATE();
SET @expiredate = DATEADD(DAY, @licenseperioddays, GETDATE());
EXEC [App].[usp_v1_add_hospital_license_details]
@hospitalid = @hospitalid,
@startdate = @startdate,
@expiredate = @expiredate,
@createdby = @createdby,
@response = @response OUTPUT;
DROP TABLE #T_HospitalLicense;
IF(@response <> 1)
BEGIN
ROLLBACK TRANSACTION;
END;
-- USER AND RESET PASSWORD REQUEST
IF OBJECT_ID(N'tempdb..#T_User') IS NOT NULL
BEGIN
DROP TABLE #T_User;
END;
SELECT FullName,
UserName,
Email,
Phone,
PasswordHash,
PasswordSalt,
UserTypeId,
AddressLine,
CountryId,
StateId,
DistrictId,
CityId,
Pincode
INTO #T_User
FROM OPENJSON(@inputjson) WITH(FullName NVARCHAR(200) '$.User.FullName', UserName NVARCHAR(100) '$.User.UserName', Email NVARCHAR(100) '$.User.Email', Phone NVARCHAR(15) '$.User.Phone', PasswordHash NVARCHAR(1000) '$.User.PasswordHash', PasswordSalt NVARCHAR(1000) '$.User.PasswordSalt', UserTypeId TINYINT '$.User.UserTypeId', AddressLine NVARCHAR(500) '$.User.AddressLine', CountryId SMALLINT '$.User.CountryId', StateId INT '$.User.StateId', DistrictId INT '$.User.DistrictId', CityId INT '$.User.CityId', Pincode INT '$.User.Pincode');
DECLARE @fullname NVARCHAR(200);
DECLARE @username NVARCHAR(100);
DECLARE @email NVARCHAR(100);
DECLARE @phone NVARCHAR(15);
DECLARE @passwordhash NVARCHAR(1000);
DECLARE @passwordsalt NVARCHAR(1000);
DECLARE @usertypeid TINYINT;
DECLARE @addressline NVARCHAR(500);
DECLARE @countryid SMALLINT;
DECLARE @stateid INT;
DECLARE @districtid INT;
DECLARE @cityid INT;
DECLARE @pincode INT;
SELECT @fullname = FullName,
@username = UserName,
@email = Email,
@phone = Phone,
@passwordhash = PasswordHash,
@passwordsalt = PasswordSalt,
@usertypeid = UserTypeId,
@addressline = AddressLine,
@countryid = CountryId,
@stateid = StateId,
@districtid = DistrictId,
@cityid = CityId,
@pincode = Pincode
FROM #T_User;
EXEC [App].[usp_v1_add_user_details]
@fullname = @fullname,
@username = @username,
@email = @email,
@phone = @phone,
@passwordhash = @passwordhash,
@passwordsalt = @passwordsalt,
@usertypeid = @usertypeid,
@hospitalid = @hospitalid,
@addressline = @addressline,
@countryid = @countryid,
@stateid = @stateid,
@districtid = @districtid,
@cityid = @cityid,
@pincode = @pincode,
@createdby = @createdby,
@response = @response OUTPUT;
DROP TABLE #T_User;
IF(@response <> 1)
BEGIN
ROLLBACK TRANSACTION;
END;
SET @response = 1;
END;
ELSE
BEGIN
SET @response = 2;
END;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
INSERT INTO [Error].[DB_Errors]
VALUES
(SUSER_SNAME(),
ERROR_NUMBER(),
ERROR_STATE(),
ERROR_SEVERITY(),
ERROR_LINE(),
ERROR_PROCEDURE(),
ERROR_MESSAGE(),
GETDATE()
);
-- Transaction uncommittable
IF(XACT_STATE()) = -1
ROLLBACK TRANSACTION;
-- Transaction committable
IF(XACT_STATE()) = 1
COMMIT TRANSACTION;
SET @response = 0;
END CATCH;
SELECT @response;
END;
USE [HMS];
GO
/****** Object: StoredProcedure [App].[usp_v1_add_hospital_license_details] Script Date: 05-02-2022 08:39:52 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author: <Bipin Kumar>
-- Create date: <07-06-2021>
-- Description: <To add the hospital license details>
-- =============================================
CREATE PROCEDURE [App].[usp_v1_add_hospital_license_details] @hospitalid BIGINT,
@startdate DATETIME,
@expiredate DATETIME,
@createdby BIGINT,
@response INT OUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO [App].[HospitalsLicenses]
(HospitalId,
LicenseStartDate,
LicenseExpireDate,
AssignedBy,
IsActive
)
VALUES
(@hospitalid,
@startdate,
@expiredate,
@createdby,
1
);
DECLARE @Id BIGINT= SCOPE_IDENTITY();
SET @response = 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
INSERT INTO [Error].[DB_Errors]
VALUES
(SUSER_SNAME(),
ERROR_NUMBER(),
ERROR_STATE(),
ERROR_SEVERITY(),
ERROR_LINE(),
ERROR_PROCEDURE(),
ERROR_MESSAGE(),
GETDATE()
);
-- Transaction uncommittable
IF(XACT_STATE()) = -1
ROLLBACK TRANSACTION;
-- Transaction committable
IF(XACT_STATE()) = 1
COMMIT TRANSACTION;
SET @response = 0;
END CATCH;
SELECT @response;
END;
USE [HMS];
GO
/****** Object: StoredProcedure [App].[usp_v1_add_user_details] Script Date: 05-02-2022 07:59:42 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author: <Bipin Kumar>
-- Create date: <07-06-2021>
-- Description: <To add the user details>
-- =============================================
CREATE PROCEDURE [App].[usp_v1_add_user_details] @fullname NVARCHAR(200),
@username NVARCHAR(100),
@email NVARCHAR(100),
@phone NVARCHAR(15),
@passwordhash NVARCHAR(1000),
@passwordsalt NVARCHAR(1000),
@usertypeid TINYINT,
@hospitalid BIGINT,
@addressline NVARCHAR(500),
@countryid SMALLINT,
@stateid INT,
@districtid INT,
@cityid INT,
@pincode INT,
@createdby BIGINT,
@response INT OUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
IF NOT EXISTS
(
SELECT Id
FROM [App].[Users]
WHERE Email = @email
OR UserName = @username
)
BEGIN
INSERT INTO [App].[Users]
(FullName,
UserName,
Email,
Phone,
PasswordHash,
PasswordSalt,
UserTypeId,
CreatedBy,
HospitalId,
AddressLine,
CountryId,
StateId,
DistrictId,
CityId,
Pincode,
IsActive
)
VALUES
(@fullname,
@username,
@email,
@phone,
@passwordhash,
@passwordsalt,
@usertypeid,
@createdby,
@hospitalid,
@addressline,
@countryid,
@stateid,
@districtid,
@cityid,
@pincode,
1
);
DECLARE @userid BIGINT= SCOPE_IDENTITY();
EXEC [App].[usp_v1_add_reset_password_request_details]
@expiringinminutes = 1440,
@username = @username,
@createdby = @createdby,
@response = @response OUTPUT;
IF(@response <> 1)
BEGIN
ROLLBACK TRANSACTION;
END;
SET @response = 1;
END;
ELSE
BEGIN
SET @response = 2;
END;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
INSERT INTO [Error].[DB_Errors]
VALUES
(SUSER_SNAME(),
ERROR_NUMBER(),
ERROR_STATE(),
ERROR_SEVERITY(),
ERROR_LINE(),
ERROR_PROCEDURE(),
ERROR_MESSAGE(),
GETDATE()
);
-- Transaction uncommittable
IF(XACT_STATE()) = -1
ROLLBACK TRANSACTION;
-- Transaction committable
IF(XACT_STATE()) = 1
COMMIT TRANSACTION;
SET @response = 0;
END CATCH;
SELECT @response;
END;
Thank You
February 5, 2022 at 2:32 pm
Hello Everyone,
I just started a developing a new application for hospitals (for maintaining hospital staff, patient, appointments etc.). I started with the database part first.
I will use APIs as back-end. So I just thought why not I should take all data in one go in JSON and try to insert it.
So here I have wrote tables and procedures which are working perfectly without any issue / problem.
Here I need to ask you respected members is this a right approach? OR there may be any better approach to achieve this.
For reference I am sharing my tables and procedures.
I request you kindly suggest in case I need to take care of any other thing related to insert, because most of the time I am going to use this approach in my application.
Thank You
It's an interesting appeal "stop me before I do it wrong" or "I dare you to find something wrong with this code" What's the specific question? Your project seems notionally about hospitals but the tables imply there's a custom implementation of identity and access management. Ha, maybe that's trickier than managing hospitals. Sending and receiving JSON to/from API's is a very common approach. Which API and data access approach(s) are you considering? Who writes the API code?
JSON is properly stored as NVARCHAR(MAX) and not NVARCHAR(3000). Regarding explicit transactions and try/catch, which encompasses which? You have an explicit transaction called from within BEGIN TRY/END TRY yet the ROLLBACK (which presumably corresponds to the transaction) is in the CATCH block. Checking XACT_STATE in the catch block is not needed (and is pedantic imo) because by definition the value will be -1 (that's why the catch block was called). And ROLLBACK doesn't interrupt the flow of control afaik (pretty sure) so you also imo could definitely look into THROW. While you looking into THROW also look into XACT_ABORT (which specifies multiple DML statements (such as INSERT's) are ALL rolled-back together as one). Also, your code inserts into a temp table and then does checks for this and that... JFDI (pronounced "jiff-dee") = just freakin do it. Transactions are for intentionally doing specific things imo. The only proper time for checks is upon insert/update/delete because everything else is BS and potential "race conditions" which are never good.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
You need to step back and do proper data modeling on all your entities. That is the most important step in proper design.
Then, as you convert the entities to physical tables, keep in mind that automatically using an identity as the clustering key for all tables is a terrible practice.
A few quick, specific, initial thoughts for:
--the Hospitals table:
HospitalId: bigint is bloated, an int is sufficient.
Hospitals will have more than one phone number, email, etc.. Therefore, I'd move Phone, Email and Fax to a separate table(s).
Likewise for the ContactPerson info. Needs to be in separate table, since you could have multiple contacts. And, btw, you need to separate the name into its relevant parts rather than store the whole name as one string.
I'm not sure 50 chars is enough for email addresses, I'd bump it up some to be safe.
AddressLine is really AddressLines, which should be stored separately. One of the few cases where AddressLine1, AddressLine2, AddressLine3 is a practical approach, even though technically it violates 1NF.
CreatedBy: bigint is bloated, int is sufficent.
ModifiedBy: bigint is bloated, int is sufficent.
Get rid of IsActive. It is a throwback to older system designs. If a hospital is not active, move it to another table. Use a view that combines active and inactive hospitals when you need to see them all. The "WHERE IsActive = 1" in nearly every query wreaks havoc on the optimizer in many cases.
You use HospitalName as a main identification item for hosptials (proc usp_v1_add_hospital_details), but you don't insure that HospitalName is unique. It appears from your overall approach that HospitalName must be unique.
--the HospitalsLicenses table:
The main clustering key should start with HospitalId, not an identity value! If more than 1 row is possible for the same HospitalId, add the identity value to make the clustering key unique.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 7, 2022 at 4:43 pm
You have an explicit transaction called from within BEGIN TRY/END TRY yet the ROLLBACK (which presumably corresponds to the transaction) is in the CATCH block. Checking XACT_STATE in the catch block is not needed (and is pedantic imo) because by definition the value will be -1 (that's why the catch block was called).
That's not true. The XACT_STATE could be 0, 1 or -1 upon entering the CATCH block.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 7, 2022 at 5:27 pm
Steve Collins wrote:You have an explicit transaction called from within BEGIN TRY/END TRY yet the ROLLBACK (which presumably corresponds to the transaction) is in the CATCH block. Checking XACT_STATE in the catch block is not needed (and is pedantic imo) because by definition the value will be -1 (that's why the catch block was called).
That's not true. The XACT_STATE could be 0, 1 or -1 upon entering the CATCH block.
Ok good so you'll be providing a counterexample. Something actually useful. Hopefully with no cursors
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply