CODEWITHSUNDEEP
c#sql-serverstored-procedures
Dot Net Dev 19
Dot Net Dev 19

Reputation: 387

Search stored procedure does not work as expected

I have written a CTE which returns some columns 

;WITH  cteResult AS
                (
                    SELECT distinct
                            I.CUSTOMER,
                            I.OFFICE,
                            I.Column1 + ' ' + I.Column2 AS ADDRESS,
                            I.POSTCODE AS ZIPCODE  
                    FROM 
                        MYTABLE I WITH (NOLOCK)
                )

I am filtering the columns returned based on the condition given below:

    SELECT 
            CUSTOMER,
            OFFICE,
            ADDRESS,
            ZIPCODE   
        FROM 
            cteResult AS CR WITH (NOLOCK)
        WHERE       
            (@pstrCustId IS NULL OR (CR.CUSTOMER = @pstrCustId))
AND 
            (@pstrAddress IS NULL OR (CR.ADDRESS = @pstrAddress))
        AND 
            (@pstrPostcode IS NULL OR (CR.ZIPCODE = @pstrPostcode))

When I test this by replacing @pstrCustId='1234' in the above select query it returns result which include custIds other than '1234' but when I comment "@pstrCustId IS NULL OR" part it returns correct result'i.e, only results with custId='1234'.

I have written a similar stored proc earlier but it worked fine. However, this particular SP does not.

Any suggestions.

Upvotes: 2

Views: 61

Answers (4)

Randolph
Randolph

Reputation: 302

Why not use Dynamic SQL? It would also help optimize the run time. First insert the result of the CTE into a temporary table, then a dynamic sql should do the trick.

;WITH cteResult as
(
    SELECT DISTINCT
        I.CUSTOMER,
        I.OFFICE,
        I.Column1 + ' ' + I.Column2 AS ADDRESS,
        I.POSTCODE AS ZIPCODE  
    FROM 
        MYTABLE I WITH (NOLOCK)
)
SELECT * INTO #TempTable FROM cteResult A

SET @DynamicSQL = 'SELECT CUSTOMER, OFFICE, ADDRESS, ZIPCODE FROM #TempTable AS CR WITH (NOLOCK) WHERE 1 = 1
    ' + (CASE WHEN @pstrCustId IS NULL THEN '' ELSE ' AND CR.CUSTOMER = ''' + @pstrCustId END) + '''
    ' + (CASE WHEN @pstrAddress IS NULL THEN '' ELSE ' AND CR.ADDRESS = ''' + @pstrAddress END) + '''
    ' + (CASE WHEN @pstrPostcode IS NULL THEN '' ELSE ' AND CR.ZIPCODE = ''' + @pstrPostcode END) + ''''

EXEC SP_EXECUTESQL @DynamicSQL

Upvotes: 0

Dmitry
Dmitry

Reputation: 518

Check that all variables are null when they don't have filter value and change your where clause:

WHERE       
    CR.CUSTOMER = ISNULL(@pstrCustId, CR.CUSTOMER)
    AND
    CR.ADDRESS = ISNULL(@pstrAddress, CR.ADDRESS)
    AND
    CR.ZIPCODE = ISNULL(@pstrPostcode, CR.ZIPCODE)

Upvotes: 0

SQLAndOtherStuffGuy
SQLAndOtherStuffGuy

Reputation: 214

Perhaps you want to return everything if @pstrCustId is NULL.

In that case.

Use (as per previous answer)

if (@pstrCustId IS NULL) 
BEGIN
    SELECT distinct
                        I.CUSTOMER,
                        I.OFFICE,
                        I.Column1 + ' ' + I.Column2 AS ADDRESS,
                        I.POSTCODE AS ZIPCODE  
                FROM 
                    MYTABLE I WITH (NOLOCK)
END
ELSE
BEGIN
    SELECT distinct
                        I.CUSTOMER,
                        I.OFFICE,
                        I.Column1 + ' ' + I.Column2 AS ADDRESS,
                        I.POSTCODE AS ZIPCODE  
                FROM 
                    MYTABLE I WITH (NOLOCK)
    WHERE       
        (I.CUSTOMER = @pstrCustId)
    END

Upvotes: 0

Killer Death
Killer Death

Reputation: 459

Why would you want to return result with desired id OR if there is none specified? Is something wrong with this?:

SELECT 
    CUSTOMER,
    OFFICE,
    ADDRESS,
    ZIPCODE   
FROM 
    cteResult AS CR WITH (NOLOCK)
WHERE       
    (CR.CUSTOMER = @pstrCustId)

Upvotes: 1

Related Questions