VALUES 之后不允许子查询? [英] Subqueries are not allowed after VALUES?

问题描述

INSERT INTO t_MT_User (ID, Badge, Name, Scope, comp_code, dept_code, [status])值 ((SELECT MAX(ID) + 1 FROM t_MT_User)，@userBadgeNumber，@userName，@userScope，@companyCode，@departmentCode，1)

此查询引发以下错误:

<块引用>

在此上下文中不允许使用子查询.只允许使用标量表达式.

如果我将 VALUES 更改为 SELECT，则会出现另一个错误:

INSERT INTO t_MT_User (ID, Badge, Name, Scope, comp_code, dept_code, [status])选择((SELECT MAX(ID) + 1 FROM t_MT_User),@userBadgeNumber、@userName、@userScope、@companyCode、@部门代码，1)

<块引用>

',' 附近的语法不正确.

在这种情况下我如何实现 (SELECT MAX(ID) + 1 FROM t_MT_User)?

解决方案

首先是第一件事 - 您的代码，即使您修复了它的语法，也是错误的.似乎您尝试实现自己的自动增量机制.那会失败.
正确的方法是使用 SQL Server 的内置自动递增机制，并将 ID 列创建为 Identity.

那么您根本不需要将它包含在插入语句中，即使在多客户端或多线程环境中也是安全的(您当前的实现将开始给出错误的结果).

INSERT INTO t_MT_User (ID, Badge, Name, Scope, comp_code, dept_code, [status]) 
VALUES ((SELECT MAX(ID) + 1 FROM t_MT_User), @userBadgeNumber, @userName, @userScope, @companyCode, @departmentCode, 1)

This query throws the following error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

If I change VALUES to SELECT, I get another error instead:

INSERT INTO t_MT_User (ID, Badge, Name, Scope, comp_code, dept_code, [status]) 
    SELECT 
        ((SELECT MAX(ID) + 1 FROM t_MT_User), 
         @userBadgeNumber, @userName, @userScope, @companyCode, 
         @departmentCode, 1)

Incorrect syntax near ','.

How do I achieve (SELECT MAX(ID) + 1 FROM t_MT_User) in this context?

解决方案

First thing is first - your code, even if you fix it's syntax, is wrong. Seems like you try to implement your own auto-increment mechanism. That will fail.
The correct way is to use SQL Server's built-in mechanism for auto-increment, and create the ID columns as an Identity.

Then you don't need to include it in the insert statement at all, and you are safe even in a multi-client or multi-threaded environments (which your current implementation will start giving wrong results).

这篇关于VALUES 之后不允许子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！