Hello,
I have a simple question. I just want to know how You save data from joined tables.
For instance:
I use the tables Vendor, VendorAdres, Customer, CustomerAdres and Adresses.
CustomerAdres and VendorAdres are tables with minimal two columns (who are Foreign Keys); the primairy keys from Adresses and Vendor(or Customer). These two records are also the primary key for that table. This is because I only want to use one adresses table for all relation types.
In my Select command I use a Join. and I am looking for the best way to save the data back to the database again. Should I use one stored procedure to update both tables, or should I use a stored procedure for each table, so I can reuse that sproc in other business objects. Which do You use.
Thank You.
Hi Bayu,
I was hoping to get the opposite answer. I'm developing the BO's and the database myself. And since I am building the database to be as clean as possible, to avoid redundancy, I want the stored procedures the same way.
These database tables will be used in many BO's. Using 1 sproc in one dataportal_call can result in multiple sproc's having the same code.
So when a database table changes, I have to find all sproc's that queries the changed table instead of 1 sproc that is used by many BO's. When a BO changes and the database does not change, there is no need to change a sproc.
What do You think about this theory?
Bayu,
Bedankt (thanx).
Sorry for my delayed response. I have had less time last week. But thank you anyway for your explanation.
Ton
Here's what I do to promote re-use of my stored procs, yet still gain the advantage of only making one db call from my business objects.
I create the usual insert, update, delete, exists, select-by-id and select all procedures, one per table.
Where needed, I hand build a stored procedure to bring back more than one table's worth of data.
For example:
create procedure ProjectGetStuff (@projectId int)
as
begin
exec ProjectSelectById @projectId
exec ProjectTasksSelectAll @projectId
exec ProjectResourcesSelectAll @projectId
end
The business object just changes the datareader from one set of data to the next.
david.wendelken:
Here's what I do to promote re-use of my stored procs, yet still gain the advantage of only making one db call from my business objects.
I create the usual insert, update, delete, exists, select-by-id and select all procedures, one per table.
Where needed, I hand build a stored procedure to bring back more than one table's worth of data.
For example:
create procedure ProjectGetStuff (@projectId int)
as
begin
exec ProjectSelectById @projectId
exec ProjectTasksSelectAll @projectId
exec ProjectResourcesSelectAll @projectId
end
The business object just changes the datareader from one set of data to the next.
Hello,
This is the kind solution I am looking for, but I don't know how to give the second stored procedure the id that is returned by the first one when using the dp_insert. I searched Google and the MSDN Forums, but could not find any answer to this.
Help would be appreciated (or maybe links or books).
Ton
Ton Smeets:
Hello,
This is the kind solution I am looking for, but I don't know how to give the second stored procedure the id that is returned by the first one when using the dp_insert. I searched Google and the MSDN Forums, but could not find any answer to this.
Help would be appreciated (or maybe links or books).
Ton
Ton,
I want to make sure that I properly understand you. You have an object that needs to insert data into a table, get the identity column from the inserted row, and pass it as a parameter to the procedure that inserts data into the second table?
Well, here's how to get the project id back from the ProjectInsert method.
CREATE procedure ProjectInsert
(@p_Name varchar(50)
,@newProjectId int output
) as
insert into Project
(Name)
values
(@p_Name)
select @newProjectId = scope_identity() return
CREATE procedure ProjectStuffInsert
(@p_Name varchar(50)
) as
declare @newProjectId int
exec ProjectInsert @p_Name, @newProjectId
// or possibly this format, I forget the syntax... :)
//exec ProjectInsert @p_Name, @newProjectId output
exec ProjectResource @newProjectId, @other_stuff_from_somewhere
How you pass in an array of project resources as a sql procedure parameter is a different matter. not sure how to do that.
I have split up customers from adresses. Because I only want 1 adress table for customers, vendors and employees. So I created 3 tables; Customers, CustomerAdresses and Adresses.
Each time when I insert a new customer, I need to insert the Customer first to get my CustomerId. Then I need to insert the new Adress (street, zip, etc), which gives me my AdressId. At last I need to save the CustomerAdress (CustomerId, AdressId, AdressType, etc)
To do this I created so far the next code:
CREATE PROCEDURE InsertAdress
(
@CustomerId int,
@Adress nvarchar(60),
@City nvarchar(60),
@ZipCode nvarchar(20),
@NewAdressId int output
)
AS
BEGIN
INSERT INTO Adresses
(adress, city, zipcode)
VALUES (@Adress,@City,@ZipCode)
SELECT @NewAdressId = adressid
FROM Adresses
WHERE adresid = SCOPE_IDENTITY()
END
CREATE PROCEDURE InsertCustomerAdress
(
@CustomerId int,
@AdressId int,
@AdresTypeId tinyint,
)
AS
BEGIN
INSERT INTO CustomerAdresses
(customerid, adressid, adresstypeid)
VALUES (@CustomerId, @AdressId, @AdresTypeId)
END
Now I would like to call these two stored procedures within 1 stored procedure. How to go from here?
CREATE PROCEDURE InsertCustomerAdressProcedure
(
@CustomerId int,
@Adress nvarchar(60),
@City nvarchar(60),
@ZipCode nvarchar(20),
@AdresTypeId tinyint,
@NewAdressId int output
)
AS
BEGIN
DECLARE @NewAdressId int
EXEC InsertAdres (@Adress, @City, @ZipCode)
EXEC InsertCustomerAdress (@CustomerId, @NewAdressId, @AdresTypeId)
END
In the last stored procedure I Declared @NewAdressId. Is @NewAdressId set by the first stored procedure? Does this have effect on the output parameter @NewAdressId? Do I need to declare @NewAdressId?
A lot of questions, I know.
Ton
I have split up customers from adresses. Because I only want 1 adress table for customers, vendors and employees. So I created 3 tables; Customers, CustomerAdresses and Adresses.
Got it!
Try this:
CREATE PROCEDURE InsertAdress
(
@Adress nvarchar(60),
@City nvarchar(60),
@ZipCode nvarchar(20),
@NewAdressId int output
)
AS
BEGIN
INSERT INTO Adresses
(adress, city, zipcode)
VALUES (@Adress,@City,@ZipCode)
// wasteful if nothing is wanted back but the id.
//SELECT @NewAdressId = adressid
//FROM Adresses
//WHERE adresid = SCOPE_IDENTITY()
// use this instead
SELECT @newAdressId = SCOPE_IDENTITY()
END
// No change to this procedure from your example.
CREATE PROCEDURE InsertCustomerAdress
To call these two stored procedures within 1 stored procedure:
CREATE PROCEDURE InsertCustomerAdressProcedure
(
@CustomerId int,
@Adress nvarchar(60),
@City nvarchar(60),
@ZipCode nvarchar(20),
@AdresTypeId tinyint,
@NewAdressId int output
)
AS
BEGIN
DECLARE @tempAdressId int
// the word output in the line below might be unnecessary, don't remember for sure.
EXEC InsertAdress(@Adress, @City, @ZipCode, @tempAdressId output)
EXEC InsertCustomerAdress (@CustomerId, @tempAdressId, @AdresTypeId)
select @newAdressId = @tempAdressId
// @tempAdressId may be unnecessary, you might be able to do the entire procedure just
// using @newAdressId. If so, delete above code line and change @tempAdressId references
// to @newAdressId.
END
Hope that helps! Didn't have time to double-check for syntax errors, but it should get you pretty close.
@David:
you can pass array's or .net datasets to SQL Server stored procedures using the image datatype.
Here is a good article on how to achieve that:
http://www.codeproject.com/cs/database/PassingArraysIntoSPs.asp
--
For more complicated objects I use views instead of stored procedures to retrieve data. It's both easier to maintain and to write dynamic (filter / search) sql queries against it.
It's also possible to make updatable views, so that your Data Access Layer can automatically generate insert and update queries. That would ofcourse be done using 'instead of' triggers for SQL Server.
thanks for the tip on arrays/datasets via images! I'll look into it.
I'm very familiar with views/instead of triggers. They are great! But I didn't know how to embed an array of child records in one, so I didn't suggest it.
David,
I think I got it. Was still wondering why to use a variable @tempAdressId, but I can see the logic in here. Do you have any links about this kind of stored procedures. Or maybe books where these stored procedures are explained.
I left the @tempAdressId parameter and worked the procedure out again. Could you tell me if I am on the right track.
CREATE PROCEDURE InsertAdress
(
@Adress nvarchar(60),
@City nvarchar(60),
@ZipCode nvarchar(20),
@NewAdressId int output
)
AS
BEGIN
INSERT INTO Adresses
(adress, city, zipcode)
VALUES (@Adress,@City,@ZipCode)
SELECT @NewAdressId = SCOPE_IDENTITY()
END
CREATE PROCEDURE InsertCustomerAdress
(
@CustomerId int,
@NewAdressId int,
@AdressTypeId int,
)
AS
BEGIN
INSERT INTO CustomerAdresses
(customerid, adressid, adresstypeid)
VALUES (@CustomerId, @NewAdressId, @AdressTypeId)
END
CREATE PROCEDURE InsertCustomerAdressProcedure
(
@CustomerId int,
@Adress nvarchar(60),
@City nvarchar(60),
@ZipCode nvarchar(20),
@AdresTypeId tinyint,
@NewAdressId int output
)
AS
BEGIN
-- DECLARE @tempAdressId int
-- Does the next sproc change the value of @NewAdressId (declared above)?
EXEC InsertAdres (@Adress, @City, @ZipCode, @NewAdressId output)
-- If so, Can I use an output parameter as input parameter as shown below?
EXEC InsertCustomerAdress (@CustomerId, @NewAdressId, @AdresTypeId)
-- SELECT @newAdressId = @tempAdressId
END
My most important questions are written in purple.
Thank you.
Ton
David,
I think I got it. Was still wondering why to use a variable @tempAdressId, but I can see the logic in here. Do you have any links about this kind of stored procedures. Or maybe books where these stored procedures are explained.
Thank you.
Ton
In some languages, you can't reference an output parameter inside the procedure except to assign it a value, i.e., you can't read a value from it within the procedure . I simply couldn't remember whether transact-sql was like that or not, and didn't have time to set up a test. After 25 something years and gobs of languages in this field, sometimes I get them a bit jumbled up. :)
I ran a short test, made a small adjustment and it worked so far.
CREATE PROCEDURE InsertCustomerAddressProcedure
(
@CustomerId int,
@Address nvarchar(60),
@City nvarchar(60),
@ZipCode nvarchar(20),
@AddressTypeId tinyint,
@NewAddressId int output
)
AS
BEGIN
EXEC InsertAddress @Address, @City, @ZipCode, @NewAddressId output
EXEC InsertCustomerAddress @CustomerId, @NewAddressId , @AddressTypeId
SELECT CA.customerid,
CA.addressid,
Ad.city,
Ad.zipcode,
CA.addresstype
FROM CustomerAddresses AS CA INNER JOIN Addresses AS Ad ON CA.addressid = Ad.addressid
WHERE CA.addressid = @addressid
END
Records are saved to both tables without problem. I know where to look now, and will read more about these stored procedures first, before moving on.
Thanx
Another question.
BEGIN
EXEC InsertAddress @Address, @City, @ZipCode, @NewAddressId outputEXEC InsertCustomerAddress @CustomerId, @NewAddressId , @AddressTypeId
Does CSLA transaction support roll-back in case the first executed stored procedure (InsertAddress ) fails. Because in that case I would propably get no @addressid back, and the second stored procedure (InsertCustomerAddress ) fails also.
Ton
Copyright (c) Marimer LLC