You could add an "OR p1.IsProfilePic IS NULL" to your WHERE clause. However, because your User_Photos.IsProfilePic column can be NULL, this could pose a problem when the User_Photos table returns more than 1 matching row.

I don't know what else your procedure is doing, but there's probably more work you need to do to get everything about the user. If this is the case, you could divide up the work by creating a temp table and populating it with the base user information. Then fire an UPDATE from the photos table to define your image. Then you could do whatever else you need to do to get your return table done. This would involve separate queries and I don't know if the complexities of what you have to do necessitate a temp table, but I figured I'd throw it out there so you could consider it in the larger context.