Create Table Generates Msg 0

Question

Post reply

Create Table Generates Msg 0

timSF

Old Hand

Points: 347
More actions
February 16, 2014 at 11:20 am

#285591

I get this error when creating a table using 'AS CASE WHEN':
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
When I remove the line marked below (containing 'AS CASE WHEN'), the table adds fine.
use CompanyData;
go
CREATE TABLE Company
(
companyIDintNOT NULL IDENTITY(1,1),
recnumintNOT NULL,
nameFormalnvarchar(100)NOT NULL,
parentnvarchar(100)NULL,
recnumParentintNULL,
sourceRefnvarchar(100)NOT NULL,
commentsnvarchar(1000)NULL,
createdBynvarchar(3)NOT NULL,
createdDatedateNOT NULL,
numEmplsintNULL,
emfValidThisCompanychar(1)NOT NULL CHECK (emfValidThisCompany IN('Y', 'N')),
nameInternal AS LOWER(nameFormal),
=> nameInternal AS CASE WHEN nameFormal LIKE 'the %' THEN SUBSTRING(nameFormal,1,LEN(nameFormal)-4) ELSE nameFormal END
PRIMARY KEY(companyID)
);

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

you are using invalid sintax. Try:

CREATE TABLE #Company(companyIDintNOT NULL IDENTITY(1,1)

,recnumintNOT NULL

,nameFormalnvarchar(100)NOT NULL

,parentnvarchar(100)NULL

,recnumParentintNULL

,sourceRefnvarchar(100)NOT NULL

,commentsnvarchar(1000)NULL

,createdBynvarchar(3)NOT NULL

,createdDatedateNOT NULL

,numEmplsintNULL

,emfValidThisCompanychar(1)NOT NULL CHECK (emfValidThisCompany IN('Y', 'N'))

,nameInternal AS LOWER(CASE WHEN nameFormal LIKE 'the %' THEN SUBSTRING(nameFormal,1,LEN(nameFormal)-4) ELSE nameFormal END)

PRIMARY KEY(companyID)

);

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

timSF Old Hand Points: 347 More actions · Answer 2

Thanks very much for your help! What's the # symbol next to the table name mean?

dogramone Hall of Fame Points: 3216 More actions · Answer 3

All the hash means is the table scope. A has means the table would only be available to the session that created it so its a temporary table.

timSF Old Hand Points: 347 More actions · Answer 4

timSF

Old Hand

Points: 347

February 17, 2014 at 5:46 pm

#1689689

Got it. Thanks guys for your help.