0

I want to add column if the column exist on table. But i my code doesn't work i get error the error message is ;

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ColumnType DEFAULT ( CASE DefaultValue WHEN NULL THEN SELECT 'NULL' ' at line 11

My Code MySql code block

CREATE DEFINER = CURRENT_USER FUNCTION `AddTableColumn` (
    TableName VARCHAR (500),
    ColumnName VARCHAR (500),
    IsPrimary TINYINT (4),
    IsNull TINYINT (4),
    DefaultValue VARCHAR (500),
    ColumnType VARCHAR (500)
) RETURNS INTEGER
BEGIN
    ALTER TABLE TableName ADD COLUMN
IF NOT EXISTS ColumnName ColumnType DEFAULT (
    CASE DefaultValue
    WHEN NULL THEN
        SELECT
            'NULL'
        ELSE
            SELECT
                DefaultValue
            END
)(
    CASE IsNull
    WHEN 1 THEN
        (SELECT 'IS NULL')
    ELSE
        SELECT
            ('IS NOT NULL')
        END
)(
    CASE IsPrimary
    WHEN 1 THEN
        (SELECT 'PRIMARY KEY')
    ELSE
        SELECT
            ''
        END
) RETURN 0;


END;

;
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
Zarzamora
  • 45
  • 1
  • 8
  • 1
    Please don't tag database engines randomly! If you are not using SQL Server, don't mention it. – Álvaro González Mar 29 '17 at 09:20
  • What ? I don't understand you ? Its a question about t-sql Syntax ! – Zarzamora Mar 29 '17 at 09:22
  • MySQL uses T-SQL? News to me.. All my life I've laboured under the misapprehension that T-SQL -> SQLServer. So.. I'm not sure which db you're actually using, but you appear to be trying to store column names as strings in a call to ALTER TABLE, and I don't know of a DB engine that supports this. Essentially, you're calling `ALTER TABLE 'mytab' ADD COLUMN 'mycol' 'varchar(500)'`.. The actual command you'd execute in a query tool would be `ALTER TABLE mytab ADD COLUMN mycol varchar(500)`. You should be looking for something more like `EXEC 'ALTER TABLE ' + mytabname + ' ADD COLUMN ' + mycol` ... – Caius Jard Mar 29 '17 at 09:23
  • @Zarzamora "*Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL (Structured Query Language) used to interact with relational databases.*" ([source](https://en.wikipedia.org/wiki/Transact-SQL)). Your error message actually says MariaDB, which is a MySQL fork. – Álvaro González Mar 29 '17 at 09:35
  • Okay thank you for your information – Zarzamora Mar 29 '17 at 09:37

0 Answers0