I inherited an application that uses some stored procedures. Here is a sample of one of the stored procedures used to insert or modify user data.
would like to get the group's opinion on the code. The SQL Server version is Microsoft SQL Server 2014 - 12.0.4100.1 (X64).
USE [MyDataBase]
GO
/****** Object: StoredProcedure [dbo].[InsertOrModifyUserData] Script Date: 2/2/2016 8:39:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertOrModifyUserData]
@userName NVARCHAR(200),
@isSuperAdmin BIT = NULL,
@modifiedBy NVARCHAR(200),
@modifiedDate DATETIME = NULL,
@isActive BIT = NULL
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
IF (@modifiedDate IS NULL OR @modifiedDate ='1900-01-01 00:00:00.000')
SET @modifiedDate = GETDATE()
IF (@isActive IS NULL)
SET @isActive = 1
IF ((@userName IS NOT NULL OR @userName <>'') AND (@isSuperAdmin IS NOT NULL OR @isSuperAdmin<>'') AND (@modifiedBy IS NOT NULL OR @modifiedBy <>''))
BEGIN
IF EXISTS(SELECT UserName FROM [dbo].[UserAccess] WHERE UserName = @userName)
UPDATE [dbo].[UserAccess]
SET IsSuperAdmin = @isSuperAdmin,
ModifiedBy = @modifiedBy,
ModifiedDate = @modifiedDate,
IsActive = @isActive
WHERE UserName = @userName
ELSE
INSERT INTO [dbo].[UserAccess] (UserName, IsSuperAdmin, CreatedDate, ModifiedBy, ModifiedDate,IsActive)
VALUES(@userName, @isSuperAdmin, GETDATE(), @modifiedBy, @modifiedDate, @isActive)
END
ELSE
RAISERROR('Required parameters are not provided or Required parameters are passed as NULL',13,1)
COMMIT TRANSACTION
select null
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
ROLLBACK TRANSACTION;
END CATCH;
END