Stored Procedure Problem

[arif101]

I am trying to get the following stored procedure to work without slowing the database down. Some records in the CallsMade table will have an OutcomeCode of 0, which isn't in the Outcomes table, or a VoiceFileName value of NULL and therefore won't bring back a result when a number is searched. Is there a way for me to bring the values up on a search even when the values are NULL?

Heres the code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO




ALTER PROCEDURE [dbo].[RPT_124_CallsAndRecordings01]
@PhoneNumber varchar(20)

AS

--Internal Variables and clean ups
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
SET NOCOUNT ON

SELECT
[HardDialler II]..CallsMade.DialledNumber AS 'DialledNumber',
CONVERT(varchar, [HardDialler II]..CallsMade.StartTime, 6) AS 'CallDate',
[HardDialler II]..Users.FirstName + ' ' + [HardDialler II]..Users.Surname AS 'AgentName',
CONVERT(varchar, [HardDialler II]..CallsMade.StartTime, 8) AS 'CallTime',
CONVERT(varchar, [HardDialler II]..CallsMade.AnswerTime, 8) AS 'AnswerTime',
CONVERT(varchar, [HardDialler II]..CallsMade.WrapupTime, 8) AS 'WrapUpTime',
CONVERT(varchar, [HardDialler II]..CallsMade.EndTime, 8) AS 'EndTime',
CONVERT(varchar, [HardDialler II]..CallsMade.WrapUpTime - [HardDialler II]..CallsMade.AnswerTime, 8) AS 'TalkTime',
[HardDialler II]..Outcomes.OutcomeText AS 'Outcome',
[HardDialler II]..VoiceRecordings.VoiceFileName AS 'VoiceFileName'

FROM
[HardDialler II]..CallsMade with (nolock)
INNER JOIN [HardDialler II]..Users with (nolock) ON [HardDialler II]..Users.UserID = [HardDialler II]..CallsMade.UserID
INNER JOIN [HardDialler II]..Outcomes with (nolock) ON [HardDialler II]..Outcomes.OutcomeID = [HardDialler II]..CallsMade.OutcomeCode
INNER JOIN [HardDialler II]..VoiceRecordings with (nolock) ON [HardDialler II]..VoiceRecordings.CallID = [HardDialler II]..CallsMade.CallsListID

WHERE
[HardDialler II]..CallsMade.DialledNumber like '%' + @PhoneNumber

ORDER BY
[HardDialler II]..CallsMade.[ID] ASC

COMMIT TRANSACTION



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[AlexCuse]

Without seeing you data it is hard to say, but you definitely need to change your WHERE clause:

WHERE
    ([HardDialler II]..CallsMade.DialledNumber like '%' + @PhoneNumber or 
[HardDialler II]..CallsMade.DialledNumber is null)

The key to remember here is that nulls will not show up if you have where column = something on that particular column. This is because NULL is unknown, SQL can't tell if it's equal or not, so it is skipped.

You also might need to change some of your joins, but try this first.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom