hi!!

I am writing sql stored procedures and am stuck on this design issue as
follows

I have a procedure named InsertIntoTbl for doing inserts.
However i need to do 2 validation steps before i can do the insert
1 step 1 check if record already exists
2 step 2 some other validation using another table

Now my question is
1)Should i club these 2 validations in my InsertIntoTbl procedure as in :-

if step1 and step2 succeed
then do insert
else
return with error

2)or should i let InsertTbl just do the insert and write the 2 validations
as 2 seperate functions which i will call from my asp.net page

Pros of this approach:
the validations are business logic
if i need to add another validation i dont have to change my insertIntoTbl
procedure

Cons:

my insert proceudre is vulnerable since it doesnt do validations.If anyone
executes it without calling the 2 validation functions,records inserted will
be inconsistent.

So please tell me your views regarding this and the best design approach to
this problem

Thanks for your help.
cooltech

[quoted text, click to view]


Yes, you should do all validation within the stored procedure, for the
exact reason you listed. Never trust anyone or any process to run things in
a certain order. Eventually that order will get out of line and things will
go wrong. If you need to maintain an order of operations, do it all in one
place. That's not just a database programming methodology -- that's a basic
OO tenet as well.


--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--