I have tried to use Case and SET NOEXEC ON but get the following errors:

Syntax Error: unexpected 'SET' (set)
Syntax Error: missing 'closing parenthesis'

INSERT INTO tag (table, repr, tag, value)
    SELECT 'product' AS table,
           @Id AS repr,
           'product_code' AS tag,
           CASE @Code       
             WHEN NOT null THEN @Code
             ELSE SET NOEXEC ON
           END AS value`

@Id and @Code are declared parameters in the script

  • 1
    please tag your question with the DBMS you're using. Also 'null' <> NULL – Max Vernon Mar 21 '18 at 17:38
  • 1
    Is there a reason you can't simply define the value column as NOT NULL? The insert would fail if you tried to INSERT a null value into that column. Also, when you say you want to stop the script, do you mean terminate with an error or simply silently do nothing? – Scott Hodgin Mar 21 '18 at 18:24
  • I cannot alter the column to be NOT NULL. And I want to terminate with an error message if possible – DBNewbie Mar 21 '18 at 18:26

SET NOEXEC ON is not MySQL syntax.

The thing to do is to avoid selecting it:

INSERT INTO tag (table, repr, tag, value)
    SELECT 'product' AS table,
            @Id AS repr,
            'product_code' AS tag,
            @Code AS value`        -- The WHERE will prevent nulls
        WHERE @Code IS NOT NULL;   -- avoid generating anything to insert when NULL
  • But this will not stop execution of script, right? – DBNewbie Mar 27 '18 at 23:40
  • @DBNewbie - If you need that, we need to see the rest of the Stored Procedure. – Rick James Mar 28 '18 at 2:12
  • There are more insert statements after this hence would need to stop script execution. Can I use BEGIN and END with exception or catch? – DBNewbie Mar 28 '18 at 22:09
  • Maybe something like IF ( SELECT ROWS_AFFECTED() ) > 0 ) THEN ... (do rest of proc) END; – Rick James Mar 29 '18 at 0:43
  • Whenever I use IF, it throws an error: – DBNewbie Mar 30 '18 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.