Problem With Stored Procedure and Associated Searchlist

Original Post: Steven Rodney 09/05/2013 19:15:05

"Within the Mattercentre database I have created a searchlist called [UDFILMAILTAB].
This is a list of fileid's with dates and flags used for emailing reminders to Fee Earners/Partners.
This list is built up using the following Stored Procedure...
IF EXISTS
(
SELECT
a.name
, a.type
FROM
sysobjects a
WHERE a.name = 'UDUpdateUDFILMAILTAB'
AND a.type = 'P'
)
BEGIN
DROP PROCEDURE [dbo].[UDUpdateUDFILMAILTAB]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UDUpdateUDFILMAILTAB]
AS
BEGIN
DECLARE @FIXED_DATE_ONE DATETIME
SET @FIXED_DATE_ONE = '2013-04-08'
-- Change @FIXED_DATE_ONE to go live date when applying to LIVE
INSERT INTO UDFILMAILTAB(fileid, calcFileInceptDate, calcEmailDate)
SELECT
a1.fileid
, a1.created AS [calcFileInceptDate]
, GETUTCDATE()AS [calcEmailDate]
FROM
config.dbFile a1
LEFT JOIN
UDFILMAILTAB b1
ON a1.fileID = b1.fileid
INNER JOIN
config.dbclient d1
ON d1.clid = a1.clid
WHERE
(LTRIM(RTRIM(a1.filestatus)) IN ('LIVE', 'LIVEA', 'TFW')) AND COALESCE(b1.fileid, '') = ''
AND ((RTRIM(LTRIM(d1.cltypecode))) != '5')
AND (RTRIM(LTRIM(a1.filetype))) NOT IN ('00-99999', '99-99999') AND ((CONVERT(NVARCHAR, a1.created, 112)) >=
(CONVERT(NVARCHAR, @FIXED_DATE_ONE, 112)))
END GO

IF NOT EXISTS (SELECT
a1.major_id FROM
sys.database_permissions a1 INNER JOIN
sys.objects b1
ON b1.object_id = a1.major_id INNER JOIN
sys.database_principals c1 ON
c1.principal_id = a1.grantee_principal_id WHERE
b1.name = 'UDUpdateUDFILMAILTAB' AND b1.type = 'P'
) BEGIN
GRANT EXECUTE
ON OBJECT::[dbo].[UDUpdateUDFILMAILTAB]
TO [OMSRole], [OMSAdminRole] END


The only dbfiles that should go into the mailing list [UDFILMAILTAB] are...
1) [dbFile].[filestatus] IS [LIVE] OR [LIVEA] OR [TFW]
2) [dbFile].[FileID] does NOT EXIST in the table [UDFILMAILTAB]
3) [dbClient].[cltypecode] IS NOT EQUAL TO [5]
4) [dbFile].[filetype] IS NOT EQUAL TO [00-99999] OR IS NOT EQUAL TO [99-99999]
5) [dbFile].[created] IS GREATER THAN OR EQUAL TO @FIXED_DATE_ONE
Unfortunately I have found that some files with [dbClient].[cltypecode] = '5' have crept into the mailing list.
I have tested the SELECT part of the Procedure again and again and no [dbClient].[cltypecode] = '5' appear, but every morning I check and find [dbClient].[cltypecode]
= '5', in my mailing list.

What am I doing wrong?"

Responses:

technical [Phil] 10/05/2013 08:26:18
"Hey Steven,
Could it be the order of your joins?
You have an outer join between dbfile and udfilmailtab and then it inner joins to dbclient.
It may be that when it query optimises the procedure that because of your outer joining its is showing files that dont also have matching dbclient matches, essentially ignoring your where clause.
If you put the dbclient then inner join to dbfile and outer join to udfilmailtab that may make the difference. i.e.
FROM
config.dbclient d1
INNER JOIN config.dbfile a1 on d1.clid = a1.clid
LEFT JOIN udfilmailtab b1 on a1.fileId = b1.fileId
Hope that helps Cheers Phil"

Steven Rodney 10/05/2013 09:04:07
"Phil
You learn something new everyday! I had no idea that order of joins could have such an effect.
I have rewritten the stored procedures to get the joins in the order you suggested. Hopefully after a few days of checking I can close down this post. Thanks."

technical [Phil] 10/05/2013 09:14:07
"Hopefully it will work, that was the only thing that jumped out at me the rest of the code looks great. I generally find put all my outer joins at the end of the list works fine in most cases.
It does depend on the chain of linking, but just like maths there is an order. The below has a good simple example http://stackoverflow.com/questions/12...
You can use join hints for the query optimiser but i've never really found a need to do so and they arent guaranteed to be applied. http://msdn.microsoft.com/en-GB/libra...
Phil"

Richard Annison 10/05/2013 10:49:00
"Hi Steven,
I have a feeling that it's not your query that's the problem in this case. Since dbFile.clID has a foreign key constraint to dbClient, it will always satisfy your join.
Could someone or some process be changing the clients type? You could try adding a cltypecode column to UDFILMAILTAB and putting the value d1.cltypecode in it. That way if it's null then the join has failed since it's a non nullable value in dbClient and if the value isn't '5' then the client type has been changed after your process. Regards
Rich"

Steven Rodney 29/05/2013 18:24:13
"I solved the problem by adding another stored procedure that removed any clientype = '5' data from the set before processing."