SQL Server stored procedure showing syntax error

Question

I have created a stored procedure which creates a table at runtime. The table columns and their data types for this dynamic table comes from another table already in place in database.

I am calling this stored procedure from C# console application code. The stored procedure is throwing a syntax error and I am totally not able to figure out what is causing this syntax error.

This is the stored procedure code I've written:

CREATE procedure [dbo].[sproc_TableExists]
   @TableName NVARCHAR(128) 
      ,@Column1Name NVARCHAR(32)
      ,@Column1DataType NVARCHAR(32)
      ,@Column1Nullable NVARCHAR(32)
AS
   DECLARE @SQLString NVARCHAR(MAX)
   BEGIN
       IF( EXISTS (select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName))
       BEGIN
            SET @SQLString = 'ALTER TABLE ' + @TableName + '( '+ @Column1Name + ' ' + @Column1DataType + ' '+ @Column1Nullable +')'
            EXEC (@SQLString)
        END
    ELSE
        BEGIN
            SET @SQLString = 'CREATE TABLE ' + @TableName + '( '+ @Column1Name + ' ' + @Column1DataType + ' '+ @Column1Nullable +')'
            EXEC (@SQLString)
        END
END
GO

Error I am getting calling it from code :

Incorrect syntax near '('.

Martin · Accepted Answer · 2014-02-24 08:48:22Z

2

The problem lies in the following line of code, when altering an existing table:

SET @SQLString = 'ALTER TABLE ' + @TableName + '( '+ @Column1Name + ' ' + @Column1DataType + ' '+ @Column1Nullable +')'

This generates the following output (as an example using a fake table\column name):

ALTER TABLE TableEx( ColumnEx NVARCHAR(MAX) NULL)

However, this is not a valid SQL statement.

If you want the behaviour to be to add a new column when the table already exists, use this instead:

SET @SQLString = 'ALTER TABLE ' + @TableName + ' ADD '+ @Column1Name + ' ' + @Column1DataType + ' '+ @Column1Nullable

Which will produce the output:

ALTER TABLE TableEx ADD ColumnEx NVARCHAR(MAX) NULL

Note that if your intention is to change the name of a pre-existing column in a pre-existing table you will need to test for this separately - the above only adds a new column to an existing table.

answered Feb 24, 2014 at 8:48

Martin

16.5k1 gold badge33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abhi Over a year ago

Thanks for your response @Martin. It solved it. What a stupid SQL mistake i was doing. Thanks again.

MusicLovingIndianGirl · Accepted Answer · 2014-02-24 08:41:51Z

1

Replace EXEC (@SQLString)

with EXEC @SQLString

answered Feb 24, 2014 at 8:41

MusicLovingIndianGirl

5,9459 gold badges38 silver badges65 bronze badges

2 Comments

Martin Over a year ago

This causes the production of another error Could not find stored procedure 'ALTER TABLE TableEx ADD ColumnEx NVARCHAR(MAX) NULL'.

Abhi Over a year ago

Thanks @Aishvarya. But that line never seemed a problem as I tested running CREATE statement separately with that EXEC line working absolutely fine.

Collectives™ on Stack Overflow

SQL Server stored procedure showing syntax error

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related