How to insert a new field before a specified field using an SQL statement

Create proc addcolumn
@ Tablename varchar (30), -- table name
@ Colname varchar (30), -- Name of the column to be added
@ Coltype varchar (100), -- column type to be added
@ Colid int -- add the column
As

Declare @ codecom_max int
Declare @ SQL varchar (1000) -- dynamic SQL statement
--------------------------------------------------
If not exists (select 1 from sysobjects
Where name = @ tablename and xtype = 'U ')
Begin
Raiserror 20001 'no such table'
Return-1
End
--------------------------------------------------
If exists (select 1 from syscolumns
Where id = object_id (@ tablename) and name = @ colname)
Begin
Raiserror 20002 'This table already has this column! '
Return-1
End
--------------------------------------------------
-- Make sure that the colid of the table is continuous.
Select @ codecom_max = max (colid) from syscolumns where id = object_id (@ tablename)

If @ colid> @ co1__max or @ colid <1
Set @ colid = @ colid + 1
--------------------------------------------------
Set @ SQL = 'alter table' + @ tablename + 'add' + @ colname + ''+ @ coltype
Exec (@ SQL)

Select @ colid_max = colid
From syscolumns where id = object_id (@ tablename) and name = @ colname
If @ rowcount <> 1
Begin
Raiserror 20003 'An error occurred while adding a new column. Check whether your column type is correct'
Return-1
End
--------------------------------------------------
-- Enable system table modification.
EXEC sp_configure 'Allow updates', 1 RECONFIGURE WITH OVERRIDE

-- Temporarily set the new column number to-1
Set @ SQL = 'Update syscolumns
Set colid =-1
Where id = object_id (''' + @ tablename + ''')
And colid = '+ cast (@ codecom_max as varchar (10 ))
Exec (@ SQL)

-- Add the column number of other columns to 1
Set @ SQL = 'Update syscolumns
Set colid = colid + 1
Where id = object_id (''' + @ tablename + ''')
And colid> = '+ cast (@ colid as varchar (10 ))
Exec (@ SQL)

-- Reset the new column number
Set @ SQL = 'Update syscolumns
Set colid = '+ cast (@ colid as varchar (10) +'
Where id = object_id (''' + @ tablename + ''')
And name = ''' + @ colname + ''''
Exec (@ SQL)
--------------------------------------------------
-- Disable system table modification.
EXEC sp_configure 'Allow updates', 0 RECONFIGURE WITH OVERRIDE
Go

Call method:
Exec addcolumn 'table name', 'new column name', 'new column type', to the position
For example:
Exec addcolumn 'test', 'id2', 'Char (10) ', 2
Add the id2 column to the second position of the test table. The type is char (10 ).