cannot make a custom checker work with lsp

[Aneeqasif]

I confirm that...

• I searched the FAQ, issue tracker, discussions board, documentation, and wiki, and found nothing about my issue.

• I read the debugging guide and researched the issue to the best of my ability.

• My issue can be observed on the latest available commit of Doom.

• My issue can be observed on a stable release of Emacs (i.e. doesn't end in .50, .60, or .9*)

Describe your issue

i am trying to add sqlfluff flycheck checker like this

(after! flycheck
  (defcustom flycheck-sqlfluff-dialect "postgres"
    "SQL dialect to use for sqlfluff checking."
    :type '(choice (const "ansi")
            (const "athena") 
            (const "bigquery")
            (const "clickhouse")
            (const "databricks")
            (const "db2")
            (const "exasol")
            (const "hive")
            (const "mysql")
            (const "oracle")
            (const "postgres")
            (const "redshift")
            (const "snowflake")
            (const "soql")
            (const "sparksql")
            (const "sqlite")
            (const "teradata")
            (const "tsql"))
    :group 'flycheck)
  
  (defun flycheck-sqlfluff-change-dialect ()
    "Change the SQL dialect for sqlfluff checker. See URL `https://www.sqlfluff.com/'."
    (interactive)
    (setq flycheck-sqlfluff-dialect 
          (completing-read "Choose sqlfluff dialect: "
                           '("ansi" "athena" "bigquery" "clickhouse" "databricks" 
                             "db2" "exasol" "hive" "mysql" "oracle" "postgres" 
                             "redshift" "snowflake" "soql" "sparksql" "sqlite" 
                             "teradata" "tsql")
                           nil t nil nil flycheck-sqlfluff-dialect))
    (message "Sqlfluff dialect set to: %s" flycheck-sqlfluff-dialect)
    (when (bound-and-true-p flycheck-mode)
      (flycheck-buffer)))
  
  ;; Define the sqlfluff checker
  (flycheck-define-checker sqlfluff
    "A SQL syntax checker using sqlfluff."
    :command ("sqlfluff" "lint" "--dialect" (eval flycheck-sqlfluff-dialect) source-inplace)
    :modes sql-mode
    :error-patterns
    ((error line-start "L:" (one-or-more space) line (one-or-more space) "|" (one-or-more space) "P:" (one-or-more space) column (one-or-more space) "|" (one-or-more space) (id (one-or-more (any "A-Z0-9"))) (one-or-more space) "|" (one-or-more space) (message (one-or-more nonl)) line-end))
    :predicate (lambda () (buffer-file-name)))
  
  (add-to-list 'flycheck-checkers 'sqlfluff))

  
(after! (sql-mode )
  (set-next-checker! 'sql-mode 'lsp 'sqlfluff)
  )

but no matter what i try either lsp checker works or sqlfluff's. I am not that experience in this matter so i seek you help

Steps to reproduce

try above code

System information

https://privatebin.net/?155069fa627a3ef8#GBCiRc58HeyU2byZvPjJo4k8qj713C2t4AANyN2W4qwJ