AndrewSQLDBA (9/20/2013)
This code is a waste, since @FirstName is an input parameter that will not allow a NULL. And the front-end should be checking the values for blanks, not the database.
IF @firstname = ''
BEGIN
RAISERROR ('[Error]No first name', 16, 1)
RETURN
END
If that variable from the front-end gets all the way to the database with a blank space, you need to look at the code on the front-end.
I would recommend that you keep that code - relying on the front end to enforce domain constraints is a mistake. You should also have the column in the table declared
FirstName varchar(50) NOT NULL CHECK(LEN(FirstName) > 0)
since that's how domain constraints are expressed in T-SQL and you should want our tables to be in 1NF, which means the table definition must specify the domain of each attribute. Since the NULL values and zero length strings are not in the domain of this attribute, the table definition should say so.
Of course it's a good idea to have the front end check these things too - it can usually give a quicker and better response to a user who makes a mistake that it detects than if it lets the mistake through for the database to detect. But that doesn't absolve the database from responsibility for data integrity.
Tom