The "best" solution is not to use dynamic sql unless there is absolutely no other way to accomplish the desired result.

Dynamic sql is a total pain to maintain. I've slogged through other people's dynamic code and many times found a better way.

If you must write dynamic sql, you should embed a sample of the resulting sql statement in the sp, to help illustrate what's going on. A very simple example of that would be:

-- use the next invoice # as the seed, might look like:

-- ALTER TABLE tblFM_MPI ADD InvoiceNumber int IDENTITY (45969, 1)

SET @sql = N'ALTER TABLE tblFM_MPI DROP COLUMN InvoiceNumber'

EXEC ( @sql )

SET @sql = N'ALTER TABLE tblFM_MPI ADD InvoiceNumber int IDENTITY (' + CAST(@NextInv AS varchar(6)) + ', 1)'

EXEC  ( @sql )