代码之家  ›  专栏  ›  技术社区  ›  Syntax Error ine

提供的列名称或值数与表定义不匹配。

  •  0
  • Syntax Error ine  · 技术社区  · 6 年前

    我已经检查了我的查询,并且列的数量和类型都是相同的(9列-临时表中的数据类型与我选择的列相同)。

    我不明白为什么会出现错误。我在同一个存储过程中使用了类似的查询,它工作正常。

    declare @HTMLSelect nvarchar(max),
    @HTMLWhere nvarchar(max),
    @timeperiod int,
    @deptcode varchar(10),
    @sql nvarchar(max)
    
    set @deptcode = null
    set @timeperiod = '-14'
    
    SET @HTMLSelect = N'SELECT TOP 200
                client.clname as [Client]
                , file.mfile as [fileNo]
                , file.mopendt as [OpenDate]
                , file.mdesc1 as [file]
                , deptlab.head1 as [Dept]
                , prac.pdesc as [fileType]
                , file.mbillaty as [Code]
                , user.tkfirst + '' '' + user.tklast AS [Username]
                , file.mrefer as [ReferralType]
                FROM         user AS user
                INNER JOIN
                    file AS file ON user.tkinit = file.mbillaty 
                INNER JOIN
                    client AS client ON client.clnum = file.mclient
                INNER JOIN
                    deptlab AS deptlab ON deptlab.delcode = file.mdept 
                INNER JOIN
                    praccode AS prac ON prac.[pcode] = file.mprac
                '
                set @deptcode = null
                set @HTMLWhere = N'
                WHERE      
                (user.tkeflag = ''Y'') 
                AND file.mrefer like ''%staff%''
                AND (file.mopendt >= DATEADD(dd, '  + CAST ( @timeperiod as [nvarchar]) +  ', GETDATE()))
                '           
        IF OBJECT_ID('tempdb..#staffTable') IS NOT NULL
            BEGIN
                Truncate TABLE #staffTable
            END
        ELSE
            BEGIN
                CREATE TABLE #staffTable(
                    Client varchar(60)
                    ,fileNo varchar(15)
                    ,OpenDate datetime
                    ,file varchar(60)
                    ,Dept varchar(40)
                    ,fileType varchar(48)
                    ,FECode varchar(8)
                    ,Username varchar(150)
                    ,ReferralType varchar(60)
                )
    
                set @sql = @HTMLSelect + @HTMLWhere
                INSERT INTO #staffTable EXEC sp_executesql @sql
    

    中查询的典型结果集 @HTMLSelect @HTMLWhere 如下:

    +----------+---------------+------------+--------+--------------+----------+------+--------------+--------------+
    | Client   | FileNo        | OpenDate   | File   | Dept         | FileType | Code | Username     | ReferralType |
    +----------+---------------+------------+--------+--------------+----------+------+--------------+--------------+
    | Client 1 | 055311.000001 | 16/07/2018 | File 1 | Department D | Type 1   | 123  | Peter Parker | Type Y       |
    +----------+---------------+------------+--------+--------------+----------+------+--------------+--------------+
    | Client 2 | 079934.000001 | 17/07/2018 | File 2 | Department A | Type 9   | 566  | Kevin Smith  | Type C       |
    +----------+---------------+------------+--------+--------------+----------+------+--------------+--------------+
    | Client 3 | 135353.005351 | 01/07/2018 | File 3 | Department V | Type 6   | 353  | Jason Mewes  | Type B       |
    +----------+---------------+------------+--------+--------------+----------+------+--------------+--------------+
    

    有人看到什么问题吗?

    我把它的各个部分都隔离了,它起作用了——它是在一起的时候断裂的!它在插入时肯定会失败,但是如果列的顺序正确,并且所有数据类型都匹配,我就不明白为什么会失败。

    使用 sp_executesql 意味着不能通过同时指定列和值来插入。

    1 回复  |  直到 6 年前
        1
  •  0
  •   Vahid Farahmandian    6 年前

    假设我们有这样的结构:

    CREATE TABLE [file]
    (
        mfile VARCHAR(15),
        mopendt DATETIME,
        mdesc1 VARCHAR(60),
        mbillaty VARCHAR(8),
        mrefer VARCHAR(60),
        mclient INT,
        mdept INT,
        mprac INT
    );
    GO
    INSERT INTO dbo.[file]
    (
        mfile,
        mopendt,
        mdesc1,
        mbillaty,
        mrefer,
        mclient,
        mdept,
        mprac
    )
    VALUES
    ('mf1', GETDATE(), 'md1', 'mb1', 'staff', 1, 1, 1);
    GO 
    CREATE TABLE [user]
    (
        tkfirst VARCHAR(50),
        tklast VARCHAR(100),
        tkinit VARCHAR(8),
        tkeflag VARCHAR(5)
    );
    GO
    INSERT INTO dbo.[user]
    (
        tkfirst,
        tklast,
        tkinit,
        tkeflag
    )
    VALUES
    ('tkf1', 'thkl1', 'mb1', 'Y');
    GO
    CREATE TABLE client
    (
        clname VARCHAR(60),
        clnum INT
    );
    GO
    INSERT INTO dbo.client
    (
        clname,
        clnum
    )
    VALUES
    ('cl1', 1);
    GO
    CREATE TABLE deptlab
    (
        head1 VARCHAR(40),
        delcode INT
    );
    GO
    INSERT INTO dbo.deptlab
    (
        head1,
        delcode
    )
    VALUES
    ('h1', 1);
    GO
    CREATE TABLE praccode
    (
        pdesc VARCHAR(48),
        pcode INT
    );
    INSERT INTO dbo.praccode
    (
        pdesc,
        pcode
    )
    VALUES
    ('pd1', 1);
    

    因此,以下代码应该有效:

    DECLARE @HTMLSelect NVARCHAR(MAX),
            @HTMLWhere NVARCHAR(MAX),
            @timeperiod INT,
            @deptcode VARCHAR(10),
            @sql NVARCHAR(MAX);
    
    SET @deptcode = NULL;
    SET @timeperiod = '-14';
    
    SET @HTMLSelect
        = N'SELECT TOP 200
           client.clname  AS [Client],
           [file].mfile  AS [fileNo],
           [file].mopendt  AS [OpenDate],
           [file].mdesc1 AS [file],
           deptlab.head1  AS [Dept],
           prac.pdesc  AS [fileType],
           [file].mbillaty  AS [Code],
           [user].tkfirst + '' '' + [user].tklast AS [Username],
           [file].mrefer AS  [ReferralType]
    FROM [user] AS [user]
        INNER JOIN [file] AS [file]
            ON [user].tkinit = [file].mbillaty
        INNER JOIN client AS client
            ON client.clnum = [file].mclient
        INNER JOIN deptlab AS deptlab
            ON deptlab.delcode = [file].mdept
        INNER JOIN praccode AS prac
            ON prac.[pcode] = [file].mprac
                ';
    SET @deptcode = NULL;
    SET @HTMLWhere
        = N'
                WHERE      
                ([user].tkeflag = ''Y'') 
                AND [file].mrefer like ''%staff%''
                AND ([file].mopendt >= DATEADD(dd, ' + CAST(@timeperiod AS [NVARCHAR]) + N', GETDATE()))
                ';
    IF OBJECT_ID('tempdb..#staffTable') IS NOT NULL
    BEGIN
        TRUNCATE TABLE #staffTable;
    END;
    ELSE
    BEGIN
        CREATE TABLE #staffTable
        (
            Client VARCHAR(60),
            fileNo VARCHAR(15),
            OpenDate DATETIME,
            [file] VARCHAR(60),
            Dept VARCHAR(40),
            fileType VARCHAR(48),
            FECode VARCHAR(8),
            Username VARCHAR(150),
            ReferralType VARCHAR(60)
        );
    END;
    
    SET @sql = @HTMLSelect + @HTMLWhere;
    
    PRINT @sql;
    
    INSERT INTO #staffTable
    EXEC sp_executesql @sql;
    
    SELECT * FROM #staffTable