With a little good luck, you will never run into having a corrupt SQL database. With a little bad luck, it will happen. Then the question becomes how bad is the corruption, and can it be repaired.
One option, of course, is to restore the database from a good backup (which you should always have). The second option is to try to recover the database. The first step, is to check the database, and determine what is needed to repair it. This is done with the "DBCC CHECKDB" command:
DBCC checkdb(
The "WITH NO_INFOMSGS" greatly simplifies the output, as only the messages relating to the corruption are displayed. In the majority of cases, this will be that a table is corrupt, and it will state what level of recovery is required. The level of recovery is used in the actual recover command, that I will get to shortly. In my experience, the "REPAIR_ALLOW_DATA_LOSS" is usually given as the minimum recover level that will work.
If it is acceptable to try the recovery, with the possible loss of, usually, a small amount of data if any, then the repair can be attempted. If it is not, then the only option at that point would be to restore from a good backup.
To attempt the data recovery, the first thing to do is to backup your database as it is. First, the database needs to be set to single-user:
ALTER DATABASE
Then, run the "DBCC CHECKTABLE" command, for each table that was reported as having corruption:
DBCC checktable(,REPAIR_ALLOW_DATA_LOSS)
When the table check finishes, it will report whether or not it was able to repair the table. If repairs were successful, then the database needs to be put back into multiuser mode:
ALTER DATABASE
Your database is now functional again. The only question remaining, is what, if any, data was lost. For this, you will have to determine how and what to check for.