Due to the overall poor support for unit testing for SQL Server (and I have found it to be the same for Oracle) often the Data Access Layer (DAL) unit tests are used to unit test the stored procedures (SPs). This breaks the principle of unit testing a single unit, however, it is a pragmatic approach as the DAL is so tightly coupled to the SPs anyway.