CharIndex problems

Question

CharIndex problems

Nollster

SSC Rookie

Points: 44
More actions
September 30, 2004 at 6:40 am

#66319

I'm working on a stored procedure (below) in which I'm trying search for a parameter value within in a field from a table returned via udf. The syntax seems to be OK, but the CharIndex value = 0 everytime. If I replace it with the actual text I'm looking for, it works -- what's happening here??
CREATE PROCEDURE dbo.spClosedProjects
@txtbegindate smalldatetime,
@txtfinaldate smalldatetime,
@txtgrant char(50) = NULL
AS
SELECT G.MENumber, G.DocNum, G.ProjectTitle,
    M.Grantee, M.GranteeAddress1, M.GranteeAddress2, M.GranteeCity,
      M.GranteeState, M.GranteeZip, M.GranteeSalutation, M.GranteeFirstName,
        M.GranteeLastName, G.TotalGrant, ISNULL(G.Budget, 0) AS Budget,
          ISNULL(I.TotalInvoices, 0) AS TotalInvoices, ISNULL(G.Budget,
            G.TotalGrant) - ISNULL(I.TotalInvoices, 0) AS Balance,
              G.FinalDate, G.ProjectAdvisor, Z.strGrantNames As GrantType, Z.strGrantIDs As GrantIDs
FROM dbo.qryTotalGrantsbyContract G LEFT OUTER JOIN
    dbo.qryGetGrantee M ON G.ApplicationID = M.ApplicationID LEFT OUTER JOIN
      dbo.qryTotalInvoicesbyContract I ON G.ContractID = I.ContractID LEFT OUTER JOIN
        dbo.fnGetGrantsList() Z ON G.ApplicationID = Z.ApplicationID
Where G.FinalDate >= @txtbegindate AND G.FinalDate <= @txtfinaldate
    AND CharIndex(@txtgrant, Z.strGrantNames) > 0
ORDER BY G.FinalDate DESC
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

KarlTheBaker Newbie Points: 9 More actions · Answer 1

When @TxtGrant is NULL, CharIndex should return a NULL value. The comparison > 0 should return false. If you want the comparison to return true when @TxtGrant is NULL then just make it a compound comparison:

(CharIndex(@TxtGrant, Z.strGrantNames>0 OR @TxtGrant is NULL)

-Karl

Nollster SSC Rookie Points: 44 More actions · Answer 2

Actually, the problem turned out to be my parameter type -- changing it to varchar solved the problem. Thanks for the input......

Amit Gupta Valued Member Points: 72 More actions · Answer 3

while using char datatype the value assigned to the @txtgrant is padded with spaces i.e. if @txtgrant = 'xyz' then it will be set to 'xyz' + 47 spaces and 'xyz' + 47 spaces will be searched in Z.strGrantNames

amit