Click here to Skip to main content
15,498,943 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I cannot get a Computed Column Specification to work. I have a SQL table named Customers. In Customers the field CustomerID is the primary key and is defined as int. I want to define another field name AccountNumber that has a computed value 'TBS-' followed by the CustomerID converted to a string. Ex: TBS-12345.

What am I doing wrong?

What I have tried:

Here is my last attempt:

SQL
AccountNumber
Computed Column Specification

 (Formula)           (isnull('TBS-'+ CONVERT (VARCHAR(10), CustomerID))) 
 Is Persisted        YES
Posted
Updated 17-Mar-22 22:39pm

ISNULL takes two parameters: SQL Server ISNULL() Function[^]
It works for me:
SQL
USE [Testing]
GO

/****** Object:  Table [dbo].[MyTable]    Script Date: 18/03/2022 07:02:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MyTable](
	[ID] [int] NOT NULL,
	[Text] [nchar](3) NOT NULL,
	[SerialNo]  AS (isnull([Text]+right('00000'+CONVERT([varchar](5),[ID],(0)),(5)),'????????')),
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
 
Share this answer
 
Comments
CHill60 18-Mar-22 10:53am    
I'm guessing the OP failed to use the correct tag on their question. IsNull in MySQL only takes 1 parameter - MySQL ISNULL() Function[^]. They should perhaps have used IFNULL. Gotta love these subtle differences - pointless differences imho
Try the following query to add computed column, change length of column accordingly:
SQL
ALTER Table Customers DROP COLUMN AccountNumber  
ALTER TABLE Customers ADD AccountNumber AS (('TBS-'+CONVERT([NVARCHAR](20),[CustomerID]))) PERSISTED
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900