Updating multiple tables in sql server
Hi Delerna, If I do not use the stored procedure, I need to update these tables through my application right? We are just creating a helper class in java(which make use of these sp's in the database and call them) and giving it to the other vendor who can actually make use of that code.
If so I need to join these 2 tables and do the insert/update from my application. Like I mentioned more than one state can have the same benefit details. In the future if the client want to implement this service class with another .
The first table stores the benefit detais as pdf file.(in the PDF_IMAGE column) more than one state is associate with the benefitdetails.(thts why we have plan_pdf table) like 2 states can have the same benefit details. I need to provide a way to the client that he can able to populate these 2 tables through stored procedures.
i am wondering that should I write a single sp to do this or I need 2 sp's to acomplish this task.
Depending on your front end it may not even be possible to use a stored proc for this. Hi Delerna, I just confirmed with my team lead that we should make use of the stored procedure, Because the font end we are using is JAVA right now.Not overly difficult but ck's suggestion is the better option by far. Here I am retrieving the pdf file based on identity column.It is difficult to give you working queries for your particular question without having access to everything that you have access to. Yes I have to get the identity column from my application, from the stored proc and have to return that number to some other method. As for other error handling, you need to think of what could go wrong and try and cover it. Hi Delerna, I can upload the pdf file directly from the java application with out using the stored procedure.(using prepared statement in java) But the problem here is I can not get the identity column value to my application which I need to return it to another application.It's easier to come up with working solutions when you can see what you are working with. For example, with your 2 inserts What if there is a record in the 2 tables and you want to insert a second,third,fourth... And one more thing when I tried to execute the sp's through my application I am only getting the out parameter incremented but the tables are not populating with values.You can see what you are working with, so you will need to find the solution. I tested by writing sp for a single insert function but the same thing its incrementing the identity column but values are not inserting in to the tables..