Post reply

Create Table Generates Msg 0

  • 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

    (

    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)

    );

  • February 17, 2014 at 3:19 am

    #1689424

    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!":-D
    (So many miracle inventions provided by MS to us...)

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

  • February 17, 2014 at 12:00 pm

    #1689622

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

  • February 17, 2014 at 12:42 pm

    #1689628

    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.

  • February 17, 2014 at 5:46 pm

    #1689689

    Got it. Thanks guys for your help.

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

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