question

There are three levels of validation that I'm aware of. First, the data type itself is a validation. If the parameters within your stored procedure are INTEGER, DATE, FLOAT, then the data passed to them must be a valid number, a real date, and a number with decimals. Secondly, assuming you have referential integrity, then the system will validate that the relationships you have within your data is valid. Lastly, you can write code to check certain things are true. With functions from CLR you can even write code that uses regular expressions to do validations. But, in general, I would expect that last type of validation to be better done at the code level. But you can do it within SQL Server.

@karz do you mean that the incoming data is populated by an external system making calls to a stored procedure? If that's the case, then your SP can be written to perform data validation and verification as the SP is called. Your SP can also be written to split data out into multiple locations, to ensure that all or nothing is done; but there's not much here that couldn't be done [using triggers][1]. This answer expands on the final option in @Grant Fritchey's answer, above, in which he talks about "writing code to check certain things are true". [1]: http://msdn.microsoft.com/en-us/library/ms189799.aspx

Grant's answer is fantastic and I second everything he said. But some people have referred to checking for SQL Injection as validation, or at least a part of validation. So, if you are looking at validation in terms of preventing SQL injection I would add a few things: 1. Avoid dynamic SQL unless you need it. 2. If you do use dynamic sql, use properly parameterized queries with sp_executesql instead of just building a sql string. If you do those two, then it will handle most cases and there is little reason to try to parse the string looking for injection attacks (though it could be useful for logging purposes, especially if you want to be able to do a post mortem on failed attacks.) Also, just to expand on what he said about "code to check that certain things are true", you certainly can do this in a stored procedure, but CHECK and other constraints can help with this and they can catch things that are inserted without going through the stored procedure. Of course, they aren't always the best approach, especially for complicated validations, but they can be useful at times.