Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help with some sql syntax.

Posted on 2016-08-30
3
Medium Priority
?
74 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
3 Comments
 
LVL 54

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

963 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