This assumes that you are using the Geography data type. If you are using the Geometry data type, use dbo.LocateAlongGeom().
Note that dbo.LocateAlongGeog() and dbo.LocateAlongGeom() are functions provided in the
SQL Server Spatial Tools Project on CodePlex. These tools were developed by the SQL Server Spatial Team and are free of charge.