Hi all,
I am trying to write the stored procedure to select the value from single table using UNION.
As follows :
table details are as folows :
This stored procedure will perform the following:-Check in database whether an exact match for the input parameters exists
o If an exact match is not found, this will return no result set and a return null in Field CDMXML as alias, indicating no match. The exact match search should be done as per two criteria:-
SSN4 should be the primary lookup value for credit score cache lookups
[Latitude] and [Longitude] should be a secondary lookup if SSN4 fails to return a result
This logic can be implemented using UNION to join output of 2 queries and selecting the top row of the result set, pseudo code example below.
o If an exact match is found (the most recent match if multiple matches are found) then
Check whether the CachedData is still valid i.e whether (Current date CatchDate) is > 12 months.
If it is greater, then the cached record is invalid and procedure will return no result set and a return alias CDMXML as NULL indicating no match.
If it is less, then the cached record is valid and the required details will be returned along with return value of 1 indicating that a match is found.
[CatchTableDataId] [bigint] IDENTITY(1,1) NOT NULL, [InterfaceMasterId] [bigint] NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [MiddleName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NOT NULL, [PrimaryPhone] [nvarchar](16) NULL, [DateOfBirth] [date] NOT NULL, [AddressLine1] [nvarchar](50) NULL, [AddressLine2] [nvarchar](50) NULL, [City] [nvarchar](50) NULL, [State] [nvarchar](40) NULL, [Latitude] [nvarchar](20) NULL, [Longitude] [nvarchar](20) NULL, [CatchDate] [date] NULL, [SourceSystem] [nvarchar](50) NULL, [ResponseCDMXML] [nvarchar](max) NULL, [SSN4] [smallint] NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT (getdate()), [LastUpdatedDateTime] [datetime] NOT NULL DEFAULT (getdate()),
Here i am doing something which is not correct in few cases :
USE [PSIC.ESB.GetCatchDB]
GO
/****** Object: StoredProcedure [dbo].[USP_GetCatchDB] Script Date: ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_GetCatchData]
(
@MaxCacheDays smallint = NULL,
@FirstName nvarchar(50)=NULL,
@MiddleName nvarchar(50)=NULL,
@LastName nvarchar(50)=NULL,
@PrimaryPhone nvarchar(16)=NULL,
@DateOfBirth date =NULL,
@AddressLine1 nvarchar(50)=NULL,
@AddressLine2 nvarchar(50)=NULL,
@City nvarchar(50)=NULL,
@State nvarchar(40)=NULL,
@SSN4 smallint=NULL ,
@Latitude nvarchar(20)=NULL,
@Longitude nvarchar(20)=NULL
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
--================================================
-- ERROR SECTION
-- ================================================
IF @SSN4 IS NULL AND @Latitude IS NULL AND @Longitude IS NULL
RAISERROR ('@ssn4 and @Latitude AND @Longitude CANNOT be empty.', 16, 1 );
--================================================
-- CODE SECTION
--================================================
Declare @XMLResult As XML
IF LEN(@SSN4)=0
SET @SSN4=NULL
IF LEN(@Latitude)=0
SET @Latitude=NULL
IF LEN(@Longitude)=0 SET @Longitude=NULL
IF LEN(@FirstName)=0 SET @FirstName=NULL
IF LEN(@MiddleName)=0 SET @MiddleName=NULL
IF LEN(@LastName)=0
SET @LastName=NULL
IF LEN(@PrimaryPhone)=0
SET @PrimaryPhone=NULL
IF LEN(@DateOfBirth)=0 SET @DateOfBirth=NULL
IF LEN(@AddressLine1)=0 SET @AddressLine1=NULL
IF LEN(@AddressLine2)=0 SET @AddressLine2=NULL
IF LEN(@City)=0 SET @City=NULL
IF LEN(@State)=0 SET @State=NULL
IF LEN(@MaxCacheDays)=0 SET @MaxCacheDays=NULL
;
WITH CatchScoreData_CTE (rank,ResponseCDMXML,CreatedTimestamp,ScoreDate)
AS (
SELECT '1' as rank , ResponseCDMXML,CreatedDateTime,CatchDate FROM [dbo].[CatchScoreData]
WHERE ((@SSN4 IS NULL) OR LTRIM(RTRIM(UPPER(SSN4))) = UPPER(@SSN4))
AND ((@FirstName IS NULL) OR LTRIM(RTRIM(UPPER(FirstName))) = UPPER(@FirstName))
AND ((@MiddleName IS NULL) OR LTRIM(RTRIM(UPPER(MiddleName))) = UPPER(@MiddleName))
AND ((@LastName IS NULL) OR LTRIM(RTRIM(UPPER(LastName))) = UPPER(@LastName))
AND ((@DateOfBirth IS NULL) OR DateOfBirth = @DateOfBirth)
AND DateDiff(D,CatchDate,getdate()) < @MaxCacheDays
UNION
SELECT '2' as rank , ResponseCDMXML,CreatedDateTime,CatchDate FROM [dbo].[CatchScoreData]
WHERE ((@Latitude IS NULL) OR LTRIM(RTRIM(UPPER(Latitude))) = UPPER(@Latitude))
AND ((@Longitude IS NULL) OR LTRIM(RTRIM(UPPER(Longitude))) = UPPER(@Longitude))
AND ((@FirstName IS NULL) OR LTRIM(RTRIM(UPPER(FirstName))) = UPPER(@FirstName))
AND ((@MiddleName IS NULL) OR LTRIM(RTRIM(UPPER(MiddleName))) = UPPER(@MiddleName))
AND ((@LastName IS NULL) OR LTRIM(RTRIM(UPPER(LastName))) = UPPER(@LastName))
AND ((@DateOfBirth IS NULL) OR DateOfBirth = @DateOfBirth)
AND DateDiff(D,CatchDate,getdate()) < @MaxCacheDays
)
SELECT TOP 1 @XMLResult = ResponseCDMXML FROM CatchScoreData_CTE ORDER BY rank ASC, ScoreDate DESC
SELECT @XMLResult as CDMXML;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorLine INT
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE();
SET @ErrorMessage = '[' + CAST( @ErrorLine AS NVARCHAR(20) ) + '] ' + @ErrorMessage
RAISERROR ( @ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END
Looking forward for your quick help on this ... 
Other recent topics
