Updateable stored proceedure with join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updateable stored proceedure with join

Author

Topic

MichaelG
Starting Member

11 Posts

Posted - 2003-05-16 : 10:31:52

I'm using RDS on a web page with a stored proceedure to get the data.

For a simple sp 'SELECT * FROM table', this provides add, update and delete capabilities through RDS.

I need to extend this for an sp based on two tables, Contacts and Addresses. The Contacts table has an Identity column, ContactID, which is a foreign key into the Addresses table.

My sp is :-
SELECT *
FROM Contacts C INNER JOIN
Addresses A ON C.ContactID = A.ContactID
WHERE ContactSetID = @ContactSetID

This works fine for updates since the ContactID is already present in both tables.

Is there any way to make this work for inserts? I need to set A.ContactID = @@IDENTITY returned when a new record is added to Contacts.

Michael

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-05-16 : 12:24:25

Hi

You need to do this in two steps.

Declare @ContactID int
INSERT INTO Contacts (blah) VALUES (blah)
Set @ContactID = @@Identity
INSERT INTO Addresses ( blah, ContactID)
VALUES (Blah, @ContactID)

Damian

MichaelG
Starting Member

11 Posts

Posted - 2003-05-16 : 13:55:16

Hi Damian,

Thanks but this wont return data to my RDS control.

I'm wondering if I change the sp into a View and add an INSTEAD OF INSERT trigger whether that would work? I'll give it a try.

I was just hopping there was a neat way to do this in the sp.

Michael

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-17 : 11:36:39

What about

SET NOCOUNT ON

Declare @ContactID int
INSERT INTO Contacts (blah) VALUES (blah)

SELECT @ContactID = @@Identity

INSERT INTO Addresses ( blah, ContactID)
VALUES (Blah, @ContactID)

Edited by - ValterBorges on 05/17/2003 11:37:14

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-05-17 : 21:17:25

What data do you want returned to RDS when you do an insert statement ? You didn't specify.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources