• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

Need help with some sql syntax.

I wrote a sql query (MS Sql Server) which is used to add a new column  to an existing table in the database. The query almost works,
however something is wrong in my sql syntax, and I can't figure it out. look at the sql between the BEGIN and END below.
I am trying to create a new column with a data type of bit. The error displayed says :
Msg 207, Level 16, State 1, Line 16
Invalid column name bit

I am stuck. Can someone tell me how to fix the sql below?

DECLARE @TableName VARCHAR(25)
DECLARE @newColumnName VARCHAR(25)
DECLARE @dbName = VARCHAR(25)
DECLARE @ddl VARCHAR(255)
SET @newColumnName = 'CustAltId'
SET @TableName = 'Customer'
SET @dbName = 'WebDock'
IF NOT EXISTS (
  SELECT * from information_schema.COLUMNS
  WHERE column_name = @newColumnName
  and table_name = @TableName
  and table_schema=@dbName
)
BEGIN
      SET @ddl = 'ALTER TABLE ' + @dbName + '.' + @TableName +
      ' ADD COLUMN ' + @newColumnName + ' ' + bit
END

EXEC @ddl
0
brgdotnet
Asked:
brgdotnet
3 Solutions
 
Ryan ChongCommented:
shouldn't it
 SET @ddl = 'ALTER TABLE ' + @dbName + '.' + @TableName +
      ' ADD COLUMN ' + @newColumnName + ' ' + bit

Open in new window

be:
 SET @ddl = 'ALTER TABLE ' + @dbName + '.' + @TableName +
      ' ADD ' + @newColumnName + '  bit'

Open in new window

or
 SET @ddl = 'ALTER TABLE ' + @dbName + '.dbo.' + @TableName +
      ' ADD ' + @newColumnName + '  bit'

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Above answer should work,  for future reference, you should use print statement instead of Select for debugging purposes, use

Print @ddl

whatever it is printing, put it on the ssms window and you can see the error . this may not work in this case
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree, the "bit" part needs to be inside quotes.
on top, I suggest to put the code like this, in order to avoid surprises on "special" column or table names:
 SET @ddl = 'ALTER TABLE [' + @dbName + '].dbo.[' + @TableName +
      '] ADD ' + @newColumnName + '  bit '

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now