Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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