Subqueries Are Not Allowed After Values?

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,

Solution 1:

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).

Solution 2:

your syntax is wrong. Should be

INSERTINTO t_MT_User (ID, Badge, Name, Scope, comp_code, dept_code, [status]) 
SELECTMAX(ID) +1 , @userBadgeNumber, @userName, @userScope, 
       @companyCode, @departmentCode, 1FROM   t_MT_User