Creo que todo lo que necesitas, derivado de lo que tu consulta debe estar devolviendo es:
Código:
SELECT NIdSucursal,
NIdBoleta,
YEAR(DEmpeno) AS Anio,
(
RIGHT(SPACE(4) + CAST(NIdSucursal AS VARCHAR(4)), 4) +
CONVERT(VARCHAR(2), DEmpeno, 2) +
RIGHT(SPACE(6) + CAST((ROW_NUMBER() OVER(PARTITION BY NIdSucursal, YEAR(DEmpeno) ORDER BY NIdBoleta)) AS VARCHAR(6)), 6)
) AS VFolio
FROM Boletas
ORDER BY NIdSucursal, YEAR(DEmpeno), NIdBoleta
Algunos comentarios tengo:
Usas conversiones y validaciones innecesarias, lo cual se puede evitar verificando el tipo de dato que las funciones que usas te entregan (Year entrega siempre un INT, ROW_NUMBER() jamás es nulo).
También puedo imaginar lo que la función SEM_FAGREGAR_CEROS_IZQUIERDA hace y estoy seguro que el valor agregado de tenerla no vale el overhead de mantenerla.
Crear y destruir estructuras temporales en un bucle. Créala fuera del bucle, trúncala para reusarla, y destrúyela al salir del bucle. Y evalúa usar variables de tabla en casos como este para reducir el costo de la transacción.
Hay muy pocos problemas que justifiquen el uso de un cursor, y este no es uno de ellos. Puesto que estás usando por lo menos SQL Server 2005, considera usar mejor CTE's, funciones de ranking y cross /outer apply en combinación.
La parte que realmente debes verificar, es el uso de tus transacciones. Puedo imaginar que sea lento tu procedimiento más por el mantenimiento del log de transacciones que por cualquier otra cosa. No tiene sentido modificar la tabla si al final harás un rollback a las modificaciones de manera incondicional ¿quizá faltó publicar algo de codigo?