Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help with some sql syntax.

Posted on 2016-08-30
3
Medium Priority
?
68 Views
Last Modified: 2016-09-01
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
Comment
Question by:brgdotnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 1000 total points
ID: 41777425
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 41777442
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 41777516
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question