Not a big fan of this procedure. But if you're going to use it, try keeping it safe and robust.

CREATE PROCEDURE sp_Truncate_Anything
(
  @DBName nvarchar(130)...

Just adding more details as you got answers. You can't create a function that will work for everything, unless you do additional work that is not really recommended. You should...

That syntax is only allowed starting on SQL Server 2012. You seem to be working on 2008, so that would be a problem.
Try using ROW_NUMBER() instead of COUNT(). In...

This gives the result you expect with the parameters shown. However, I suggest that you do a more extensive testing as I might have missed something.

CREATE...

Why not use NULLIF with a COUNT?

DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);
INSERT INTO @ClosedStuff
VALUES (NULL),(NULL),('19000101'),(GETDATE())

SELECT COUNT(NULLIF( InspectionClosed, '19000101')) FROM @ClosedStuff


Have you compared the performance of the given solution with simply appending the searched character at the end of the string?

Select name, Substring(name,0, Charindex('.',name +'.')) as...

Since there's no way to give a proper answer without further information, I'll just leave this article here that explains this kind of problem.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/