Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Table Generates Msg 0 Expand / Collapse
Author
Message
Posted Sunday, February 16, 2014 11:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 17, 2014 5:45 PM
Points: 10, Visits: 29
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
(
companyID int NOT NULL IDENTITY(1,1),
recnum int NOT NULL,
nameFormal nvarchar(100) NOT NULL,
parent nvarchar(100) NULL,
recnumParent int NULL,
sourceRef nvarchar(100) NOT NULL,
comments nvarchar(1000) NULL,
createdBy nvarchar(3) NOT NULL,
createdDate date NOT NULL,
numEmpls int NULL,
emfValidThisCompany char(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)
);
Post #1541917
Posted Monday, February 17, 2014 3:19 AM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
you are using invalid sintax. Try:
CREATE TABLE #Company(	companyID				int		NOT NULL IDENTITY(1,1)
, recnum int NOT NULL
, nameFormal nvarchar(100) NOT NULL
, parent nvarchar(100) NULL
, recnumParent int NULL
, sourceRef nvarchar(100) NOT NULL
, comments nvarchar(1000) NULL
, createdBy nvarchar(3) NOT NULL
, createdDate date NOT NULL
, numEmpls int NULL
, emfValidThisCompany char(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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1542017
Posted Monday, February 17, 2014 12:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 17, 2014 5:45 PM
Points: 10, Visits: 29
Thanks very much for your help! What's the # symbol next to the table name mean?
Post #1542241
Posted Monday, February 17, 2014 12:42 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 587, Visits: 2,834
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.


Post #1542247
Posted Monday, February 17, 2014 5:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 17, 2014 5:45 PM
Points: 10, Visits: 29
Got it. Thanks guys for your help.
Post #1542318
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse