Solved

Need help with some sql syntax.

Posted on 2016-08-30
3
56 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 52

Accepted Solution

by:
Ryan Chong earned 250 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 50 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 200 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

710 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