What's wrong with my `ALTER TABLE` statement?

Question

I keep receiving the error:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'as'.

My code

ALTER TABLE [SupplierDetails]

ADD [practice] varchar(300) as CONCATENATE(SupplierDescription, SupplierEmail)

Answer 1

I don't know of any SQL Server function called CONCATENATE() although there is one called CONCAT(). In any case, you can just use the plus operator to achieve what you want:

ALTER TABLE [SupplierDetails]
ADD [practice] as (SupplierDescription + SupplierEmail)

As this SO answer mentions, you might be better off handling the concatenation in a SELECT or UPDATE statement.

Answer 2

First of all CONCATENATE(SupplierDescription, SupplierEmail) this is not a valid function in SQL server.

Try this

ALTER TABLE [SupplierDetails]

ADD [practice] varchar(300) 

update SupplierDetails
set practice=SupplierDescription + SupplierEmail

or

ALTER TABLE [SupplierDetails]

ADD [practice] AS (SupplierDescription + SupplierEmail)

Answer 3

Try this.

ALTER TABLE [SupplierDetails]
ADD [practice] AS CONCAT(SupplierDescription,' ',SupplierEmail)

OR

ALTER TABLE [SupplierDetails]
ADD [practice] AS (SupplierDescription+SupplierEmail)

OR

ALTER TABLE [SupplierDetails]
ADD [practice] AS CONCAT(SupplierDescription,SupplierEmail)

Answer 4

I assume you want to create a computed column, which is fine.

The issue is that SQL Server doesn't have a function, called CONCATENATE, you either need to use a + sign, or use CONCAT(). In order to make your ALTER TABLE statement correct, you have to do this:

If your SQL Server version is 2008 R2 or older, which doesn't support CONCAT():

ALTER TABLE SupplierDetails
ADD practice AS LEFT(SupplierDescription + SupplierEmail, 300);

If you're using SQL Server 2012 and beyond:

ALTER TABLE SupplierDetails
ADD practice AS LEFT(CONCAT(SupplierDescription, SupplierEmail), 300);

Keep in mind that computed columns have to be persisted, that's why I'm adding LEFT(xxx, 300);, to make sure that your computed column won't be longer than 300 characters.

Please see this in action:

https://data.stackexchange.com/stackoverflow/query/429734/computed-column