You are making this needlessly complicated. You are trying to build a dynamic query where a static one would do just fine. Assuming the logic in the dynamic SQL is correct, the entire procedure could look like this:
CREATE PROCEDURE test.ZTEST1
SET NOCOUNT ON;
PARSENAME(REPLACE(SUBSTRING(@Messages, CHARINDEX(':', @Messages) + 1, LEN(@Messages)), ':', '.'), 4);
/* or you could split the complex expression into multiple lines,
for potentially easier understanding of the logic:
CHARINDEX(':', @Messages) + 1,
I have replaced
@Messages as that seemed an obvious mistake (and may have been the cause of an error message very similar to the one you have posted).
On a different note, the way you are calling the stored procedure:
is acceptable only if that statement is first in the batch. The more universal way of calling stored procedures that would work regardless of where the statement was located would be using the
EXECUTE test.ZTEST2 'test1:test2:test3:test4:test5';
Finally, the name of the procedure you are trying to execute,
test.ZTEST2, does not exactly match the name of the one being created above (
test.ZTEST1) – so, you need to fix that too.