Generic Update /Delete Stored Procedure

[FoxWing]

Hi experts,

I'm starting a project using Foxpro with MSSQL database. FOR MSSQL side, i'm now in the middle of creating stored-procedures. My question is should i write 1 single generic Update sp (if possible) or I have to write diffrent sp for all different FROM and WHERE clause.

B'cos the system is so huge and have different Delete and Update conditions, this could ending-up writing 100 sp.

If so, before i'm going to spend weeks for that, i need to know whether am i in the right track.

Please advice.

Thanks

 

[hneal98]

IMHO, it is always better to take large projects and break them down, but if you are just mean that you have a lot of data, just doing the one update would be fine.

 

[FoxWing]

hneal98,

I'm intending to purely use SP for all SQL statement. Therefore, there will be no inline SQL statament in my application.

This mean sp will gonna contains all the SQL DML operation. However, if u suggest 1 SP would be sufficient, How to achieve that ?

Example :
1) Update Cust Set name_1 = w_name1 where custID = 'ABC'
2) Update Cust Set name_1 = w_name1 where custID = 'ABC' and company_id = '1'
3) Update Cust Set name_1 = w_name1, name_2 = w_name2 where custID = 'ABC' and <condition2> and <condition3>


How to suit these 3 SQL into one UPDATE Stored-procedure ?


I'm lost now.
Help.

 

[hneal98]

I only suggested using one Update if you just meant there was a lot of data, but it looks to me like you have a lot of different types of update statements. And on top of that, if you are calling these from an application you wrote, like VB.Net, etc., then you have to seperate them out unless you can think of a creative way of writing your SP, like using parameters and dynamic sql.