c# - Search stored procedure does not work as expected -


i have written cte returns columns

;with  cteresult                 (                     select distinct                             i.customer,                             i.office,                             i.column1 + ' ' + i.column2 address,                             i.postcode zipcode                                                mytable (nolock)                 ) 

i filtering columns returned based on condition given below:

    select              customer,             office,             address,             zipcode                         cteresult cr (nolock)                            (@pstrcustid null or (cr.customer = @pstrcustid)) ,              (@pstraddress null or (cr.address = @pstraddress))         ,              (@pstrpostcode null or (cr.zipcode = @pstrpostcode)) 

when test replacing @pstrcustid='1234' in above select query returns result include custids other '1234' when comment "@pstrcustid null or" part returns correct result'i.e, results custid='1234'.

i have written similar stored proc earlier worked fine. however, particular sp not.

any suggestions.

check variables null when don't have filter value , change clause:

where            cr.customer = isnull(@pstrcustid, cr.customer)     ,     cr.address = isnull(@pstraddress, cr.address)     ,     cr.zipcode = isnull(@pstrpostcode, cr.zipcode) 

Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -