SPs to update multiple tables

[prettitoni]

Hi. I've got one table with multiple children tables off it. I've got individual SPs for each of the tables that either updates the record or insert it if it doesn't exist. I have to update the tables thru VB6 and I'm not sure about something. Can someone tell me if its better to have another SP (like a main one) that accepts all the parameters and passes them to the appropriate sub SPs for the updates...or set up each SP thru my VB code and call each of them depending on what data is passed?

 

[NoCoolHandle]

In my opinion, you are better to have a single proc that you call from your vb app that takes all the parameters you need and then executes multiple smaller procs inside sql server.

That strategy has a number of advantages over calling multiple procs from your vb app. Reduced Network traffic (just one call), less objects in memory (on the client), better abstraction (I dont' know how it happens, but when you pass all the data it ends up in the correctplaces), ease of administration for security (only the top level proc needs permissions)

You are still ahead in from the SQLserver endwhen it comes to breaking down your storedproc into a series of smaller procs. Code reuse being one of the main ones, but another very good reason is that it is often easier to follow small distinct blocks of code than to have to look at a couple of thousand lines as a single block

For what it is worth


Rob

 

[prettitoni]

Okay thanks. That's what I will do

 

[donutman]

Rob, got my vote. Partly because I've had trouble trying to do the main SP process in a VB procedure. I haven't found a way to use the same command object with various SPs. Somehow its associated parameter object doesn't get refreshed (using CommandObject.Parameters.Refresh) with each SP, so I was forced to use a different command object with each SP.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[prettitoni]

Yeah, I've had the same problem with the Refresh method...

I've got another question about this main SP that I'm creating....

Say for one value, I pass in NULL and the value in one of the tables was previously 2...I do want this updated to NULL. Because of this, I cannot use the logic that if the parameter passed in is NULL, don't call the SP because I need it to call it even if the value is NULL. Can u guys tell me how then can I check and decide which SP to call? Do u understand what I'm saying?

Should I have some other parameter like 'CallSP_in' that tells which SP to call and check on that?

 

[NoCoolHandle]

Just for the heck of it, why don't you pass it a string (varchar(4)) with the value of 'Null'

it isn't null, but if you test the parameter and find it to be null, then you pass an explicit Null to the update.

Rob

 

[NoCoolHandle]

Sorry forgot to put quotes around teh second null in the second sentence..
should have read

it isn't null, but if you test the parameter and find it to be [green]'null'[/green], then you pass an explicit Null to the update.


You don't need to have 'Null' being the word, but it kind of makes sense based on what you are wanting to do.

 

[prettitoni]

You mean pass the word 'Null' to the main SP that will then pass it to the appropriate individual SP?

I'd rather have the blank spaces than the word 'Null' tho...not sure if it makes a difference performance wise.

How else can I check in the main SP to tell which individual procedure to update?

 

[prettitoni]

You know what...I can't do that explicit word 'null' thing...some of the parameters are integers and won't accept that. I can't have 0 either...0 is meaningful in this case.

 

[SQLSister]

Not sure about the vb end of this, but what we do is create the parameter with a default value (in this case null) then only pass the parameters we want to the sp.

If you have a default value on the parameter and only want to pass say two out of five parameters, you pass it like this in Query analyzer:

Exec usp_test @value2 = 1, @value1 = 'test'

Note thje order of the parameters no longer matters. Of course you will still have to explicitly pass any parameters with a default value.

I know our .Net programmers have no trouble handling an sp this way, I;m sure there must be a regular vb method to do this too.



Questions about posting. See faq183-874

 

[NoCoolHandle]

How about having a default value for your paramteres that is completely out of line and test for that.

Then you could pass a null

ie for an int field use -32,1000 (assuming you will never get passed -32,100) for a char '~' or somthing like that.

 

[prettitoni]

SQLSister:
So its like an optional parameter? How then, in the stored procedure do you check if the value has been passed in or not?

NoCoolHandle:
I thought of that....I'll consider it but I don't like all that useless data being in my tables....

 

[SQLSister]

If you have a default value the that is the value ofthe parameter.

so I have @test1 with a vlaue of 'test' and @test2 wasnot passed in so it has a value of null

Insert into table1 (field1, field2)
Values (@test2, @test2)

should work. Of course you table has to be able to accept whatever the default value is.

IN no cool handle's solution you would do it something like this
If @ test2 = -32,1000
Set@test2 = null

Then run the insert statement afterward. So you aren;t inserting the wrong info, just telling yourself that it needs special handling.


Questions about posting. See faq183-874

 

[NoCoolHandle]

What she said

Hey sister. Congrats on the tipmaster ot week!

Also just to add to it.. Don't forget your "begin"'s and "end"'s after a "if" or it might not work the way you are expecting..

ie if @someparam ='the default'
begin
return 1
[green]-- sends back a 1 indicating (internal)
--that nothing needed to be don via the
--return parameter. This can be retrieved
--in the client or tested in you next proc
[/green]
end
else
begin
update x set y = @somparam where f=@someotherparam
End

 

[SQLSister]

Thank you.
Yep, I agree on the begin and end. You don't need then for one line of code, but it sure makes maintenance easier!

Questions about posting. See faq183-874

 

[prettitoni]

Aright. Thanks to both of you. I'll try it out and I always put BEGIN/END, even with one line of code

 

[NoCoolHandle]