What is sp_prepare for?

Transact-SQL
6

Usually sp_prepare is used (internally) from client code, e.g., java code. If you have a java code segment such as this (copied from wiki)

java.sql.PreparedStatement stmt = connection.prepareStatement(
               "SELECT * FROM users WHERE USERNAME = ? AND ROOM = ?");
stmt.setString(1, username);
stmt.setInt(2, roomNumber);
stmt.executeQuery();

That then internally calls sp_prepare. See here. Very rarely have I seen calls to sp_prepare in raw T-SQL code.