Stored Procedures
-
Hi i want to design database that have different object but with the same structure . we have feeling table with this structure (code,name,id) also zone table with (code,name,id). my question is ,i have to design separate procedure for insert & update & delete or i can use one procedure for both table ?? is it possible to send table name?? is it good way to send table name from DAL OR BLL layer? please help me
-
Hi i want to design database that have different object but with the same structure . we have feeling table with this structure (code,name,id) also zone table with (code,name,id). my question is ,i have to design separate procedure for insert & update & delete or i can use one procedure for both table ?? is it possible to send table name?? is it good way to send table name from DAL OR BLL layer? please help me
Keep it simple. Create 2 stored procedures. Just my opinion. :java:
-
Keep it simple. Create 2 stored procedures. Just my opinion. :java:
tnx david.but i have 8 table with this structure .we have to design (delete,update,insert,SearchByName,SearchCode,....)store procedure .now what is the best way??
-
Hi i want to design database that have different object but with the same structure . we have feeling table with this structure (code,name,id) also zone table with (code,name,id). my question is ,i have to design separate procedure for insert & update & delete or i can use one procedure for both table ?? is it possible to send table name?? is it good way to send table name from DAL OR BLL layer? please help me
You can use dynamic SQL but I would not suggest that. Create the stored procedures. It is easy to use cut and past for creating all you need, and if one table changes you only change on procedure. Another way would be to pass the table name into the procedure and have an IF ELSE IF structure within the procedure to update the tables.
-
tnx david.but i have 8 table with this structure .we have to design (delete,update,insert,SearchByName,SearchCode,....)store procedure .now what is the best way??
Since the data structure of the table seems to be identical across these 8 tables, why don't you create a "super table" which contains the name of the table, followed by the other columns ? Data would look like: Feeling,code,name,id Zone,Code,name,id Table6,Code,name,id This way you would only implement a single set of Select,Insert,Upadate,Delete, just passing the name of the table as part of the "Where" clause. Kind of hard to describe, but hopefully you get the idea. :cool: Good luck. :thumbsup:
-
tnx david.but i have 8 table with this structure .we have to design (delete,update,insert,SearchByName,SearchCode,....)store procedure .now what is the best way??
Mahdi_kishislan wrote:
now what is the best way??
The best way would be a design where each table has a specific structure optimized for the data it is holding. By using a dump-table where all has the same structure, you'll loose a lot of advantages that a usual database offers - simple things like references and type-safety.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Mahdi_kishislan wrote:
now what is the best way??
The best way would be a design where each table has a specific structure optimized for the data it is holding. By using a dump-table where all has the same structure, you'll loose a lot of advantages that a usual database offers - simple things like references and type-safety.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
I totally agree. It seems that his design is kind of lacking. I just tried answering his question. Sounds more like a homework assignment than a real project.
-
Since the data structure of the table seems to be identical across these 8 tables, why don't you create a "super table" which contains the name of the table, followed by the other columns ? Data would look like: Feeling,code,name,id Zone,Code,name,id Table6,Code,name,id This way you would only implement a single set of Select,Insert,Upadate,Delete, just passing the name of the table as part of the "Where" clause. Kind of hard to describe, but hopefully you get the idea. :cool: Good luck. :thumbsup:
tnx
-
Since the data structure of the table seems to be identical across these 8 tables, why don't you create a "super table" which contains the name of the table, followed by the other columns ? Data would look like: Feeling,code,name,id Zone,Code,name,id Table6,Code,name,id This way you would only implement a single set of Select,Insert,Upadate,Delete, just passing the name of the table as part of the "Where" clause. Kind of hard to describe, but hopefully you get the idea. :cool: Good luck. :thumbsup:
tnx david
-
Hi i want to design database that have different object but with the same structure . we have feeling table with this structure (code,name,id) also zone table with (code,name,id). my question is ,i have to design separate procedure for insert & update & delete or i can use one procedure for both table ?? is it possible to send table name?? is it good way to send table name from DAL OR BLL layer? please help me
I Think You Should Add One More Column To Your Main Table As Type Now Your Table Structure Is (Code,Name,ID,Type) Now In Type Column You Can Insert Feeling Or Zone. And Now One One Insert Update And Delete Procedure Can Do The Same, As You Required. ------------------------------------------------------------------- Another Approach You Should Pass Table Name To Your Procedure And Create Dynamic Query. To Execute That Dynamic Query Use SP_ExecuteSQL Hope This Will Help You.......