Q:

Problem adding leading zeros to a nested SELECT value using SQL Server 2016

I am trying to add leading zeros to an nvarchar data type value that needs to be a length of 9 but I'm not having any luck with my attempts. I am building a flat file and every field is an nvarchar data type.  
Here is my original SQL code:
SELECT (SELECT MAX([Record Number])+1 FROM #tempFINAL) as [Record Number]

After doing some googling here is attempt #1:
SELECT RIGHT(REPLICATE('0', 9) + (SELECT MAX([Record Number]+1) FROM #tempFINAL), 9) as [Record Number]

Here is attempt #2:
SELECT RIGHT('000000000' + (SELECT MAX([Record Number]+1) FROM #tempFINAL), 9) as [Record Number]

Neither attempts worked.  Any help/direction would be appreciated.  I must be missing something fairly easy.  Thanks.

A:

Sean is correct, your mixing a string with an int.  Here are 4 quick options:
Select [Record Number] = Format(max([Record Number])+1,'000000000') from #tempFINAL

Select [Record Number] = right(concat('000000000',max([Record Number])+1),9) from #tempFINAL

Select [Record Number] = right('000000000'+convert(varchar(10),max([Record Number])+1),9) from #tempFINAL

Select [Record Number] = right('000000000'+left(max([Record Number])+1,10),9) from #tempFINAL

