SQL statement tip: Use or to make logical judgments when querying

First look at the following SQL logical statement block:

DECLARE @fieldname    varchar( -)DECLARE @fieldvalue nvarchar( -)SET @fieldname='Chassisno' --Here you can pass in Chassisno,plateno,owner,contacttelno one of them or do not passSET @fieldvalue='Zuowenjun'IF @fieldname = 'Chassisno'BEGIN    SELECT *  fromTABLENAMEWHEREChassisno=@fieldvalueENDELSE IF @fieldname = 'Plateno'BEGIN    SELECT *  fromTABLENAMEWHEREPlateno=@fieldvalueENDELSE IF @fieldname = 'owner'BEGIN    SELECT *  fromTABLENAMEWHERE [owner]=@fieldvalueENDELSE IF @fieldname = 'Contacttelno'BEGIN    SELECT *  fromTABLENAMEWHEREContacttelno=@fieldvalueENDELSEBEGIN    SELECT *  fromTABLENAMEEND

Now if you need to use a SQL statement to achieve the above logical judgment and query results, you may think of the following stitching method to achieve:

DECLARE @sqltext NVARCHAR( -)SET @sqltext='SELECT * from TABLENAME WHERE' + @fieldname + '=" " + @fieldvalue +" '"EXECUTE(@sqltext)

Although this seems to be able to achieve logical judgment and query results, but it does not seem intuitive, modify the trouble and error prone, and there are certain limitations, because here @fieldname and table fields are the same, splicing relatively easy, if not the same situation, it will not be implemented, so I use another method here , efficiency aside, but absolutely easy to use and flexible enough, see the following SQL statement:

SELECT *  fromTABLENAME AWHERE 1=1 --(if other conditions are required, otherwise, you can do this or not, if you do not, then the first and need to be removed) and((@fieldname = 'Chassisno'  andA.chassisno=@fieldvalue)OR(@fieldname<>'Chassisno') ) and((@fieldname = 'Plateno'  andA.plateno=@fieldvalue)OR(@fieldname<>'Plateno') ) and((@fieldname = 'owner'  andA.[owner]=@fieldvalue)OR(@fieldname<>'owner') ) and((@fieldname = 'Contacttelno'  andB.contacttelno=@fieldvalue)OR(@fieldname<>'Contacttelno') )

It is verified that the SQL statement can implement logical judgment and query the results, and even if the @fieldname and table fields are not the same, we can also change the corresponding field directly, now to briefly explain its principle:

and (@fieldname = ' chassisno ' and [email protected]) OR (@fieldname <> ' Chassisno '))

Because it is and association, so the conditions inside the parentheses are must be satisfied, and because the parentheses inside the or association, so the two sides of the parentheses in the conditions can be met a can, translation is, if @fieldname = ' Chassisno ', then ask [email Protected], otherwise as long as @fieldname<> ' Chassisno ', the previous association condition is ignored. Note that their characteristics are mutually exclusive, that is, or both sides of the conditions can only meet one of them, do not know that we do not understand, of course, if there are better methods or different views welcome exchanges, Common progress!

SQL statement tip: Use or to make logical judgments when querying