inserting/updating multiple tables through stored procedures

[srinivas gandrath]

Hi all,

I am having trouble to writing stored procedure to insert and update the 2 tables in sql server 2005. Here is my problem.

I have 2 tables with following columns.
TableName:BENEFIT_PDF
1)DOCUMENT_CK
2)EFFECTIVE_DATE
3)PDF_FILE_NAME
4)PDF_DISPLAY_NAME
5)PDF_IMAGE

another TableName: PLAN_PDF
1)DOCUMENT_CK
2)PLAN_ID
3)STATE

Here document_ck is the identity column which is autogenerated and refer to the PLAN_PDF.
EFFECTIVE_DATE and PDF_FILE_NAME FORM THE UNIQUE INDEX.

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.PLAN_ID is unique in the table.

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.

I aslo need to provide the way to update these fields with the existing data at any time.
I am new to storedprocedures and I am confusing in mapping these tables and populate them.

Please help me.

thanks
Srini

[Delerna]

Hi Shrinivas.
You wouldn't normally use a stored procedure, although it certainly can be done.
Stored procs may or may not be your best choice
A lot depends on what you are using as the front end for your users.

To do it with a stored proc, your front end will need to call it and pass the values to the stored proc through parameters.

create proc Update2Tables
         @Action varchar(50),
         @Doc int,
         @Dte datetime,
         @FileName varchar(50),
         @DispName varchar(50),
         @Image  varchar(50),
         @Plan int,
         @State  varchar(50)
as
IF @Action='Update'
BEGIN
     --SQL to update the 2 tables appropriately
END
 
IF @Action='Insert'
BEGIN
     INSERT INTO BENEFIT_PDF
     SELECT @Doc,@Dte,@FileName,@DispName,@Image
 
     INSERT INTO PLAN_PDF
     SELECT @Doc,@Plan,@State 
END
 
go
 

You would call it something like this

exec Update2Tables 'INSERT',1,'2008-01-01','Test1','Test2','Image1',1,'STATE'


I have given you a basic idea here.
As I said Stored proc may not be the best way, it depends on a lot of things?
Depending on your front end it may not even be possible to use a stored proc for this.

[ck9663]

Try creating a trigger that will do the cascade insert for you...

-- CK

[srinivas gandrath]

Hi Delerna,

If I do not use the stored procedure, I need to update these tables through my application right?

If so I need to join these 2 tables and do the insert/update from my application.

Can you send me sql query to join these 2 tables. Like I mentioned more than one state can have the same benefit details.

How can I do it? through join or any other way?

Please help me.

[srinivas gandrath]

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.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.In the future if the client want to implement this service class with another .NET or some other language they can still use the sp's in the database and they can write their own business logic in the application from front end.

So I should stick to the stored procedures now.

the problem is that joining these two tables in the stored procedure.

My application is based on Health Care.Client wants a functionality that when the user login we should provide an option as Benefit details in the portal.When the user clicks in he should be presented all the pdf links that he is eligible for. Then user clicks on any of the link he should be able to view that pdf.

There are two tables which I created one is BENEFIT_PDF, second is PLAN_PDF.
As I said before more than one state can have the same benefit details.
and client should able to populate these tables with same benefit details but different states.
I am wondering how could I join these tables so that client can populate these tables.

Please send me the code for the joining these tables(with specified condition) in the stored procedure so that I can write front end application.

I am writing a helper class through which client can upload the customer benefit details into the database tables.

[ck9663]

Create an updateable view.

1. Create a view that join these tables.

2. Create an INSTEAD OF INSERT trigger on the view.

3. Let your trigger do the INSERT to two tables.

-- CK

[Delerna]

Ditto to what ck said.

But if you really want to use stored proc then you can easily call it with parameters from java. Then you just make the stored proce do whatever you want it to do using those parameters.

You can also use

   IF EXISTS (select * from TheTable where ConditionsToDetermineInsertOrUpdate)
   BEGIN
       --sql code to update
   END ELSE BEGIN
       --sql code to insert
   END
 

instead of the parameter to do inserts or updates.

Then you have the issue of returning the recordset back to JAVA for re-display.
Not overly difficult but ck's suggestion is the better option by far.

It is difficult to give you working queries for your particular question without having access to everything that you have access to. It's easier to come up with working solutions when you can see what you are working with.

You can see what you are working with, so you will need to find the solution.
We can help you along the way though.

[srinivas gandrath]

[quote=Delerna]Ditto to what ck said.

Hi Delerna,

Thanks for your reply. Here I am doing some think like you said before.Please check the code I have writen.



# create proc Update2Tables
# @Action varchar(50),
# @Doc int OUTPUT,
# @Dte datetime,
# @FileName varchar(50),
# @DispName varchar(50),
# @Image image,
# @Plan int,
# @State varchar(50)
# as
# IF @Action='Update'
# BEGIN
# UPDATE BENEFIT_PDF SET EFFECTIVE_DATE=@Dte,PDF_FILE_NAME=@FileName,PDF_DI SPLAY_NAME=@DispName,PDF_IMAGE=@Image
# WHERE DOCUMENT_CK=@Doc
# UPDATE PLAN_PDF SET PLAN_ID=@Plan,STATE=@state
# WHERE DOCUMENT_CK=@Doc
# END
#
# IF @Action='Insert'
# BEGIN
# INSERT INTO BENEFIT_PDF VALUES(@Dte,@FileName,@DispName,@Image)
# SELECT @Doc=SCOPE_IDENTITY()
#
# INSERT INTO PLAN_PDF VALUES(@Plan,@State)
# SELECT DOCUMENT_CK=@Doc
# END
#IF @Action='Select'
# BEGIN
# SELECT PDF_IMAGE FROM BENEFIT_PDF WHERE DOCUMENT_CK=@Doc
# END




Does this code work for me? I am not handling any errors in the code.So please suggest me how to handle the errors in sp.

Here I am retrieving the pdf file based on identity column.

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.

Thanks in advance.

[Delerna]

Does this code work for me?

I don't know! when you run the front end and perform some tests, does it work the way you expect?
The code seems OK

please suggest me how to handle the errors in sp

One thing you could do is wrap the two inserts in a TRANSACTION
That way if either one fails you can roll both back to the state they were in before the transaction began.

   begin tran t1
      insert etc etc
      insert blah blah
   if @@error<>0    
   begin         
      rollback tran t1        
   end else begin         
      commit tran t1   
   end
 

As for other error handling, you need to think of what could go wrong and try and cover it.
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... record into the PLAN_PDF table.
How are you going to handle that?

[srinivas gandrath]

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.

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.

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..

And the condition which you mentioned that if I need to populate the second table with different states for the same benefit details.

I am going crazy by thinking about that condition to implement but I could not move forward ..I simply stuck.

Please give me an idea how could I achieve that condition and also correct me what am I doing wrong with stored procedure that is not updating the tables.

The same java code works for me when I use it with out sp's.

Here I am sending the sp I have written.

USE [Test1]
GO
/****** Object: StoredProcedure [dbo].[Update2Tables] Script Date: 11/18/2008 16:43:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Update2Tables
@Action varchar(50),
@Doc int OUTPUT,
@Dte datetime,
@FileName varchar(50),
@DispName varchar(50),
@Image image,
@Plan int,
@State varchar(50)
AS
IF @Action='Update'

DECLARE @update_error int

BEGIN TRY

BEGIN TRANSACTION

UPDATE BENEFIT_PDF SET EFFECTIVE_DATE=@Dte,PDF_FILE_NAME=@FileName,PDF_DI SPLAY_NAME=@DispName,PDF_IMAGE=@Image
WHERE DOCUMENT_CK=@Doc

UPDATE PLAN_PDF SET PLAN_ID=@Plan,STATE=@state
WHERE DOCUMENT_CK=@Doc

SELECT @update_error=@@ERROR
IF @update_error=0
BEGIN

COMMIT TRANSACTION

END
END TRY

BEGIN CATCH
IF @update_error<>0
BEGIN

ROLLBACK TRANSACTION

END

END CATCH

IF @Action='Insert'

DECLARE @insert_error int

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO BENEFIT_PDF VALUES(@Dte,@FileName,@DispName,@Image)
SELECT @Doc=SCOPE_IDENTITY()

INSERT INTO PLAN_PDF VALUES(@Plan,@State)
SELECT DOCUMENT_CK=@Doc

SELECT @insert_error=@@ERROR
IF @insert_error=0
BEGIN

COMMIT TRANSACTION

END

END TRY

BEGIN CATCH
IF @insert_error<>0
BEGIN

ROLLBACK TRANSACTION

END

END CATCH

IF @Action='Select'
BEGIN

SELECT PDF_IMAGE FROM BENEFIT_PDF WHERE DOCUMENT_CK=@Doc

END

I can use a single try catch to handle the errors but here I just use the seperate
try catches to be more clear but I will change it later.

Please help me in this how could I achieve that same benefit details for different states.

Thanks in Advance.

[Delerna]

Very busy at the moment. It will look more thorougly when things quieten down a bit.

[srinivas gandrath]

Oh it is ok. I am sorry I did not know that you were busy.Any way Thank you very much for letting me know that and when ever you will find a time you can give me a Reply.

Thanks a million.

[Delerna]

OK, got some free time now.
The INSERT INTO statements look wrong.

Something more like this
[code=sql]
INSERT INTO BENEFIT_PDF (NameOfField1,NameOfField2,NameOfField3,NameOfFiel d4)
VALUES(@Dte,@FileName,@DispName,@Image)
[code]

or this

INSERT INTO BENEFIT_PDF
SELECT @Dte,@FileName,@DispName,@Image
 

Afterthought
Maybe the parameters are empty and therefore the record is being created with the fields having no value just like the parameters.

Try using query analyser with this code

 exec Update2Tables 'Insert','2008-01-01','DocName.pdf','Display Name',1,'NSW'
 

If the result of that is a record in the table with the field vales set correctly, then you know that the problem is in the front end and not the stored proc.

If the result is the same as when run from the front end then the problem is in the stored proc. In that case copy one of the insert statements and paste it into query analyser. Replace the parameters with values and try executing it.
I feel you will get an error message because they don't look right to me.

Whenever you have problems getting a large task to work, always break the problem down into smaller pieces and get that small piece working. Then you can put the working pieces back together into the larger task, knowing that that smaller piece is working.
Make sense?

[Delerna]

Now to the problem of inserting extra records into the PLAN_PDF table.

In the INSERT condition of the stored proc
If you provide enough info in your parameters you can check to see if a record already exists in BENEFIT_PDF if not then a record needs to be inserted into both tables.
If a record does exist then you only need to insert the record into the PLAN_PDF table.

You will, of course, need to be sure that the single record in BENEFIT_PDF properly references the multiple records in PLAN_PDF.

Hope these hints help you to solve your issues.