问题描述:

I have a table with a field: UserProjectNumber, this is just a int field that needs to increment by one everytime. I currently increment this number using the following INSERT statement. This works perfectly as long as there is already a record in the table for this user. If this is the first time the statement is run it fails because it can't add 1 to NULL

INSERT INTO UserProject (UserID, UserProjectName, UserProjectNumber)

VALUES (2, 'Test', CASE WHEN (SELECT MAX(UserProjectNumber) FROM UserProject WHERE UserID=2)= NULL THEN 0 ELSE (SELECT MAX(UserProjectNumber) FROM UserProject WHERE UserID=2)+1 END )

I am using MS SQL Server 2012. Can anybody suggest a way to resolve this? Perhaps somehow converting the NULL to a 0?

Thanks

网友答案:

Mayhaps this will do:

    declare @UserProject as Table
      ( UserProjectId Int Identity, UserId Int, UserProjectName VarChar(32), UserProjectNumber Int );

    insert into @UserProject ( UserId, UserProjectName, UserProjectNumber ) values
      ( 2, 'Test', ( select IsNull( Max( UserProjectNumber ), 0 ) + 1 from @UserProject where UserId = 2 ) );
    insert into @UserProject ( UserId, UserProjectName, UserProjectNumber ) values
      ( 2, 'Alpha', ( select IsNull( Max( UserProjectNumber ), 0 ) + 1 from @UserProject where UserId = 2 ) );
    insert into @UserProject ( UserId, UserProjectName, UserProjectNumber ) values
      ( 1, 'Aleph', ( select IsNull( Max( UserProjectNumber ), 0 ) + 1 from @UserProject where UserId = 1 ) );
    insert into @UserProject ( UserId, UserProjectName, UserProjectNumber ) values
      ( 2, 'Beta', ( select IsNull( Max( UserProjectNumber ), 0 ) + 1 from @UserProject where UserId = 2 ) );
    insert into @UserProject ( UserId, UserProjectName, UserProjectNumber ) values
      ( 1, 'Beth', ( select IsNull( Max( UserProjectNumber ), 0 ) + 1 from @UserProject where UserId = 1 ) );

    select *
      from @UserProject
      order by UserId, UserProjectNumber;

Maintenance of the UserProjectNumber column becomes problematic when users are allowed to delete projects. Should the numbers remain dense? Should new projects reuse old numbers?

If the purpose of adding the UserProjectNumber is solely for display, then it probably makes more sense to either generate a number on the fly using Row_Number() over ( partition by UserId order by UserProjectName ) or leave it up to the application.

If the purpose is to enforce the limit of six projects per user then a constraint using Count(), or a trigger, may be a better choice.

网友答案:
INSERT INTO UserProject (UserID, UserProjectName,  UserProjectNumber) 
VALUES  (2, 'Test', ISNULL((SELECT MAX(UserProjectNumber) FROM UserProject WHERE UserID=2),0)+1)
相关阅读:
Top