Zachary Yolles Zachary Yolles - 3 months ago 9
SQL Question

inserting data using max +1 for a single record not the table

I need to update a row based on 2 parameters. My query so far will work using MAX for the field in question when working off the table.
I am having difficulty limiting the field to the record needed. The parameters are @ClientCode and @BillSeq to select the proper record

This is the query that works for the table

DECLARE @ClientCode char(4), @BillSeq int, @BillCommentSeq smallint, @Billcomment varchar(7500)
SELECT
@ClientCode = '00NJ'
,@BillSeq = 1
,@BillCommentSeq = (select MAX(BillCommentSeq) +1 from billcomment )
,@Billcomment = (Select convert (varchar,getdate (),10) +' '+ 'Re-invoiced bill adjusting fees to 0' )


INSERT into dbo.BillComment VALUES (@ClientCode, @BillSeq, @BillCommentSeq, '' , @Billcomment)


I expected the query below to work however it does not

DECLARE @ClientCode char(4), @BillSeq int, @BillCommentSeq smallint, @Billcomment varchar(7500)
SELECT
@ClientCode = '00NJ'
,@BillSeq = 1
,@BillCommentSeq = (select MAX(BillCommentSeq) +1 from billcomment where clientcode = @ClientCode and billseq = @BillSeq )
,@Billcomment = (Select convert (varchar,getdate (),10) +' '+ 'Re-invoiced bill adjusting fees to 0' )


INSERT into dbo.BillComment VALUES (@ClientCode, @BillSeq, @BillCommentSeq, '' , @Billcomment)


I get this error:


Cannot insert the value NULL into column 'BillCommentSeq', table
'database.dbo.BillComment'; column does not allow nulls. INSERT fails.
The statement has been terminated.


Using this modified statement

DECLARE @ClientCode char(4), @BillSeq int, @BillCommentSeq smallint, @Billcomment varchar(7500)
SELECT
@ClientCode = '00NJ'
,@BillSeq = 1
,@BillCommentSeq = (select MAX(BillCommentSeq) from billcomment) +1 where clientcode = @ClientCode and billseq = @BillSeq )
,@Billcomment = (Select convert (varchar,getdate (),10) +' '+ 'Re-invoiced bill adjusting fees to 0' )


INSERT into dbo.BillComment VALUES (@ClientCode, @BillSeq, @BillCommentSeq, '' , @Billcomment)


The error is this:


Incorrect syntax near the keyword 'where'.

Answer
DECLARE @ClientCode char(4), @BillSeq int, @BillCommentSeq smallint, @Billcomment varchar(7500)
    Set            @ClientCode = '00NJ'
    Set            @BillSeq = 1

SELECT @BillCommentSeq=Max(BillCommentSeq) FROM billcomment where clientcode = @ClientCode and billseq = @BillSeq
    IF @BillCommentSeq Is NUll 
         SET @BillCommentSeq=1
    Else 
        Select @BillCommentSeq = Max(BillCommentSeq) + 1 FROM billcomment where clientcode = @ClientCode and billseq = @BillSeq 

  Select   @Billcomment =  (Select convert (varchar,getdate (),10) + 'Re-     invoiced bill adjusting fees to 0' )
  Print @Billcomment
INSERT into BillComment
Select @ClientCode,@BillSeq,@BillCommentSeq,@Billcomment
Select * from BillComment