I am attempting to string together insert statements in a stored procedure...

In tblCatalogue there is all the information on catalogues.

In tblArticles there is all the information on articles.

There is a key between the articles table and the catalogues, that reflect which catalogue a specific article belongs in.

I am trying to add a new article and column at the same time in a stored procedure. If a new record is created in the Catalogue table can that KeyCol value then be passed into the Insert statement for the Article table? Would this be done with a "SELECT KeyCol FROM INSERTED" trigger on the Catalogue table? If so how do you trap that output in a variable to be passed to the article insert?

All help and comments are appreciated.

I'm not clear on the relationship between articles and catalogs. Must an article be in a catalog? Can an article be in more than one catalog?

It would also help to know what database engine you are targeting (Db2, Oracle, MS-SQL, etc). This can influence many design choices.

-PatP

Apologies. I am using MSSQL 2000. An article can only appear in one catalog and must appear in a catalog.

The key here is that I am calling a SP, usp_ins_Article. Passed into the SP are two values, ArticleName, and CatalogName. The stored procedure checks to see if the CatalogName exists and if not it calls usp_ins_Catalog, passing in the catalog name. Once that is complete I need to have the value of the KeyCol for the new record. This is then inserted into the table (tblArticles) as a foreign key along with the ArticleName.

What I am hoping for is to have the Catalog SP return the KeyCol value into a variable in the Articles SP.

I hope this makes sense, and thanks for your quick reply.

If your insert stored procedure concocts the KeyCol value using code, just keep a copy. If it uses an IDENTITY column, recover the value using @@IDENTITY.

In both your CREATE PROCEDURE paramenter list and your EXECUTE parameter list for the insert procedure, include the OUTPUT modifier for a parameter to return the value to the caller.

That should get you on your way!

-PatP