CREATE Procedure spGetReturnCheckForCriteria@.SearchCriteria VARCHAR(8000),
@.SortOrder VARCHAR(8000),
@.PageSize INT
AS
-- Declare vars
DECLARE @.SQLStatement NVARCHAR(4000)
DECLARE @.bldSQLStatement VARCHAR(8000)
DECLARE @.retValue INT
-- Initialize vars
SET @.SQLStatement = ''
SET @.bldSQLStatement = ''
SET @.retValue = -1
-- Sanity Check(s)
IF (@.PageSize IS NULL OR @.PageSize < 1)
-- Paging Size can not be Null, nor less than One
BEGIN
SET @.RetValue = -30 -- "Must have a valid Paging Size for pagination: Error (-30)
RETURN
END
-- Build the Paging SQL Statement
SET @.bldSQLStatement = 'SELECT TOP '
-- Add the Page Size
SET @.bldSQLStatement = @.bldSQLStatement + CAST(@.PageSize AS VARCHAR)
-- Add Columns/Tables/Relationships
SET @.bldSQLStatement = @.bldSQLStatement + '
ReturnCheck.ReturnCheckID AS ReturnCheckID,
ReturnCheck.FiscalNumber AS FiscalNumber,
ReturnCheck.ReturnedDate AS ReturnedDate,
ReturnCheck.CheckNumber AS CheckNumber,
ReturnCheck.AssessPenaltyIndicator AS AssessPenaltyIndicator,
ReturnCheck.ReturnCheckCollectionStatusCode AS ReturnCheckCollectionStatusCode,
ReturnCheck.ReturnCheckReasonCode AS ReturnCheckReasonCode,
ReturnCheck.CentralCollectionsID AS CentralCollectionsReferralNumber,
TaxPayment.PaymentID AS PaymentID,
TaxPayment.DocumentLocatorNumber AS DocumentLocatorNumber,
TaxPayment.PaymentEffectiveDate AS PaymentEffectiveDate,
TaxPayment.PaymentAmount AS PaymentAmount,
TaxPayment.PaymentQuarter AS PaymentQuarter,
TaxPayment.PaymentYear AS PaymentYear,
TaxPayment.InternalReferenceNumber AS InternalReferenceNumber,
TaxPayment.PaymentTypeCode AS PaymentTypeCode,
TaxPayment.PaymentOriginCode AS PaymentOriginCode,
TaxPayment.VoucherNumber AS VoucherNumber,
TaxPayment.ReversedIndicator AS ReversedIndicator,
TaxPayment.PaymentDate AS PaymentDate,
CAST(NULL AS DATETIME) AS CCReferralDate,
DistributionPoint.UIDPrime AS UIDPrime,
DistributionPoint.UIDCheck AS UIDCheck,
DistributionPoint.UIDDistPoint AS UIDDistPoint,CASE
WHEN ReturnCheck.UpdatedDate IS NULL THEN ReturnCheck.CreatedDate
ELSE ReturnCheck.UpdatedDate
END AS ReturnCheckTimeStamp
FROM TaxPayment
INNER JOIN DistributionPoint
ON (TaxPayment.DistributionPointID = DistributionPoint.DistributionPointID)
INNER JOIN ReturnCheck
ON (TaxPayment.PaymentID = ReturnCheck.PaymentID)
'
-- Add Search Criteria
If (@.SearchCriteria IS NOT NULL)
SET @.bldSQLStatement = @.bldSQLStatement + ' WHERE ' + @.SearchCriteria
-- Add Sort Order
IF (@.SortOrder IS NOT NULL)
SET @.bldSQLStatement = @.bldSQLStatement + ' ' + @.SortOrder
-- Set the SQLStatement
SET @.SQLStatement = @.bldSQLStatement
-- Execute the Paging Query
EXECUTE @.retValue = sp_executeSQL @.SQLStatement
GO
Look at the SQL build where I'm doing an INNER JOIN between TaxPayment and RefundCheck. Instead of this INNER JOIN, I'd like to do a union instead. If I can get any help on this I'd greatly appreciate it. Cheers.In order for a UNION to make sense, the tables should have equivalent columns. That does not appear to be the case here. What are you trying to do?|||Thanks for the reply Douglas. The reason I want to do a union is because sometimes there won't be any records in the table ReturnCheck with the same ID as a record in TaxPayment. Because of this issue, I won't get any records returned to me.
Initially, the sproc had a LEFT OUTER JOIN between TaxPayment and ReturnCheck, but that was causing problems with other methods, so it was changed to an INNER JOIN. Now those problems are fixed, but it's causing my problem to occur.
I thought maybe a UNION would solve the problem, but the SQL is being built dynamically so it would be an extreme pain to implement.|||The LEFT OUTER JOIN is the solution to what you are trying to do. You do not indicate what the problems with "other methods" were, but if they were related to handling NULLs, then I would work around those problems. NULLs are your friend, they mean data is missing.
