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