Passing Index Value

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

Passing a parameter from a query in a link to another report

Hi,
I am trying to link to reports together, I want to be able to pass a ID
number returned from a query in this link. THe link currently looks
like this:
http://offsite/ReportServer?/LondonResults/Detailed Report by
LEA&LEA=(Fields!IDNumber.Value)&rs:Parameters=true
But in my parameter box in the second report the value I am getting is
(Fields!IDNumber.Value), when really I wan to see an ID number.
ANy ideas?
StephenI'm not exactly sure where you are entering it but if it is an expression it
should be something like:
="http://offsite/ReportServer?/LondonResults/Detailed Report by LEA&LEA="
&(Fields!IDNumber.Value) & "&rs:Parameters=true"
Neil
"stephen.adams@.forvus.co.uk" wrote:
> Hi,
> I am trying to link to reports together, I want to be able to pass a ID
> number returned from a query in this link. THe link currently looks
> like this:
> http://offsite/ReportServer?/LondonResults/Detailed Report by
> LEA&LEA=(Fields!IDNumber.Value)&rs:Parameters=true
> But in my parameter box in the second report the value I am getting is
> (Fields!IDNumber.Value), when really I wan to see an ID number.
> ANy ideas?
> Stephen
>