SQL statement skills: cleverly use OR to achieve logical judgment when querying

First look at the following SQL logical statement block:

DECLARE @fieldname    varchar(50)
DECLARE @fieldvalue nvarchar(100)
SET @fieldname='chassisno' --这里可传入chassisno，plateno，owner，contacttelno其中之一或不传
SET @fieldvalue='Zuowenjun'
IF @fieldname = 'chassisno'
BEGIN
    SELECT * FROM TABLENAME WHERE chassisno=@fieldvalue
END
ELSE IF @fieldname = 'plateno'
BEGIN
    SELECT * FROM TABLENAME WHERE plateno=@fieldvalue
END
ELSE IF @fieldname = 'owner'
BEGIN
    SELECT * FROM TABLENAME WHERE [owner]=@fieldvalue
END
ELSE IF @fieldname = 'contacttelno'
BEGIN
    SELECT * FROM TABLENAME WHERE contacttelno=@fieldvalue
END
ELSE
BEGIN
    SELECT * FROM TABLENAME
END

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

DECLARE @sqltext NVARCHAR(2000)
SET @sqltext='SELECT * FROM TABLENAME WHERE ' + @fieldname + '=''' + @fieldvalue +''''
EXECUTE (@sqltext)

Although this seems to be possible. Logical judgment and query results, but it seems not intuitive enough, modification is troublesome and error-prone, and has certain limitations, because @fieldname here is the same as the table field, so splicing is relatively easy. If they are not the same, it cannot be achieved. , so I adopted another method here. Let’s not talk about the efficiency for now, but it is absolutely easy to use and flexible enough. Please see the following SQL statement:

SELECT * FROM TABLENAME a WHERE 1=1 --(如果需要其它条件则用其它条件,否则可以这样或不要,如果不要则下面的第一个AND需去掉)
AND ((@fieldname = &#39;chassisno&#39; AND a.chassisno=@fieldvalue) OR (@fieldname<>&#39;chassisno&#39;) )
AND ((@fieldname = &#39;plateno&#39; AND a.plateno=@fieldvalue) OR (@fieldname<>&#39;plateno&#39;) )
AND ((@fieldname = &#39;owner&#39; AND a.[owner]=@fieldvalue) OR (@fieldname<>&#39;owner&#39;) )
AND ((@fieldname = &#39;contacttelno&#39; AND b.contacttelno=@fieldvalue) OR (@fieldname<>&#39;contacttelno&#39;) )

It has been verified that this SQL statement can realize logical judgment and query results, and Even if @fieldname is different from the table field, we can also directly replace the corresponding field. Now let’s briefly explain the principle:

AND ((@fieldname = 'chassisno' AND a.chassisno=@fieldvalue) OR (@ fieldname<>'chassisno') )

Because it is an AND association, the conditions in the following brackets must be met, and because the OR association is used in the brackets, one of the conditions on both sides of the brackets can be met. To translate, if @fieldname = 'chassisno', a.chassisno=@fieldvalue is required. Otherwise, as long as @fieldname<>'chassisno', the previous associated conditions will be ignored. Note that their characteristics are mutually exclusive, that is, the conditions on both sides of OR can only meet one of them. I don’t know if you understand it. Of course, if you have better methods or different opinions, welcome to exchange and make progress together!