Simple computed columns? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple computed columns?

Author

Topic

RobDeManc
Starting Member

7 Posts

Posted - 2012-08-29 : 06:38:47

Hi this is I'm sure a simple question for which I have searched for the answer all morning but cannot get one.

I have a table 'ORDERS' for which a column 'ORDER_NUM' needs to default to the record ID (which is the primary key), and the string 'ORD'.

I have SQL Server 2008 R2 and it will not let me add a computed column specification. I have tried ('ORD' + [ID]), ('ORD',[ID]), ('ORD',ID).

It tells me there is an error in the forumula. If I try to create a default value it simply puts my formula into any new orders that get created.

The database was recently exported from ACCESS and the default value for this field was not imported to SQLServer.

Any help will will be appreciated. Thanks.

Rob

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-29 : 06:59:09

ALTER TABLE dbo.Orders ADD Order_Num AS ('ORD' + CAST(ID AS VARCHAR(12))) PERSISTED

N 56°04'39.26"
E 12°55'05.63"

RobDeManc
Starting Member

7 Posts

Posted - 2012-08-29 : 07:36:48

Hi thanks

I tried this: ALTER TABLE dbo.ORDERS ALTER COLUMN ORDER_NUM AS ('ORD' + CAST(ID AS VARCHAR(12))) PERSISTED

IT SAYS: error evaluating formula for column. It won't let me save the change either. It says I must drop the table.

Rob

RobDeManc
Starting Member

7 Posts

Posted - 2012-08-29 : 07:57:04

I tried this now:

ALTER TABLE dbo.ORDERS ADD DEFAULT 'ORD' + [ID] FOR [ORDER_NUM];

The message comes back:

The name "ID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Rob

RobDeManc
Starting Member

7 Posts

Posted - 2012-08-29 : 08:56:59

OK. I ended up creating a trigger for this and it works.

This is it but I wonder if there was a simpler way?

CREATE TRIGGER [dbo].[NewOrderTrigger]
ON [dbo].[ORDERS]
AFTER INSERT
AS
BEGIN
DECLARE @orderID INTEGER;
select @orderID=i.ID from inserted i;

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
UPDATE ORDERS SET ORDER_NUM='ORD' + CONVERT(Varchar(12), @orderID) WHERE ID=@orderID;

END

Rob

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-29 : 09:02:51

quote:
Originally posted by RobDeManc

Hi thanks

I tried this: ALTER TABLE dbo.ORDERS ALTER COLUMN ORDER_NUM AS ('ORD' + CAST(ID AS VARCHAR(12))) PERSISTED

IT SAYS: error evaluating formula for column. It won't let me save the change either. It says I must drop the table.

Rob

Don't use the TABLE DESIGNER!!!
It's a T-SQL statement that you should run in a query window in SQL Server Management Studio or en editor of your choice.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-29 : 09:05:06

Drop the trigger and see this piece of code

CREATE TABLE	#Sample
		(
			ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
		);
GO

INSERT	#Sample
DEFAULT	VALUES
GO 2

SELECT	*
FROM	#Sample
GO

ALTER TABLE #Sample ADD Order_Num AS ('ORD' + CAST(ID AS VARCHAR(12))) PERSISTED
GO

SELECT	*
FROM	#Sample
GO

INSERT	#Sample
DEFAULT	VALUES
GO 2

SELECT	*
FROM	#Sample
GO

DROP TABLE	#Sample
GO

Now drop your trigger and run the ALTER TABLE statement again...

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-29 : 09:06:36

Your trigger is flawed because you have written in it a way that you assume inserts only happens with one and only one row at any given time.
What if you insert 10 rows in one batch? Then your trigger will only update one of these 10 rows.

See my example above and understand what it does.

N 56°04'39.26"
E 12°55'05.63"

RobDeManc
Starting Member

7 Posts

Posted - 2012-08-29 : 09:07:21

quote:
Originally posted by SwePeso

quote:
Originally posted by RobDeManc

Hi thanks

I tried this: ALTER TABLE dbo.ORDERS ALTER COLUMN ORDER_NUM AS ('ORD' + CAST(ID AS VARCHAR(12))) PERSISTED

IT SAYS: error evaluating formula for column. It won't let me save the change either. It says I must drop the table.

Rob

Don't use the TABLE DESIGNER!!!
It's a T-SQL statement that you should run in a query window in SQL Server Management Studio or en editor of your choice.

N 56°04'39.26"
E 12°55'05.63"

Hi thanks

It wouldn't let me do that either. I had to create a trigger in the end. It complained about use of the ID field and I couldn't find another way to do it. If you know an easier way please let me know because I have to do this to 5 other tables.

Rob

RobDeManc
Starting Member

7 Posts

Posted - 2012-08-29 : 09:11:13

Thanks SwePeso I will try that in that before I change the other tables.

Rob

RobDeManc
Starting Member

7 Posts

Posted - 2012-08-29 : 09:47:18

Ok thanks swepeso - Your advice worked but I had to drop the existing column and then add it again which made me a bit nervous. Thanks again.

Rob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources