Passing a value to another stored procedure...need urgent help

Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.

Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.

The error i get is: Procedure or function 'SPOne' expects parameter '@.publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.

SPOne is as follows:

ALTER PROCEDUREdbo.StoredProcedureOne @.typeIDsmallint=null, @.titlenvarchar(MAX)=null, @.publicationIDsmallint OUTPUTASBEGINSET NOCOUNT ON INSERT INTOPublication (typeID, title) VALUES(@.typeID, @.title) SELECT@.publicationID =scope_identity()

END

and SPtwo is as follows:

ASDECLARE@.NewpublicationIDIntEXECStoredProcedureOne @.NewpublicationID =OUTPUTSET@.publicationID = @.NewpublicationIDINSERT INTOPublicationAuthors (publicationID, authorID)VALUES(@.publicationID, @.authorID)

SELECT@.NewpublicationID

Thanks

Gemma

Is there any reason for separating them instead of putting the INSERTs into one proc?

|||

For SP 2, try this:

exec StoredProcedureOne null, null, @.publicationId = @.NewpublicationID output

|||

Thanks for your responses

Mark - it doesnt seem to like the syntax when putting the nulls in?

Dinakar - I have tried in one procedure but had trouble with arrays as i need to pass in multiple values from listbox for the second insert (publicationAuthors) so i gave up and wanted to try this way instead...and now im still stuck :(

|||

What exactly is the error when you use nulls?

Try this, for grins:

exec StoredProcedureOne @.typeId = 0, @.title='test', @.publicationId = @.NewpublicationID output

|||

with the nulls i just get 'Incorrect Syntax near @.publicationID'

with your last selection - exec StoredProcedureOne @.typeId = 0, @.title='test', @.publicationId = @.NewpublicationID output

i get the error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Publication_PublicationType". The conflict occurred in database "C:\INETPUB\WWWROOT\SOSYM WEBSITE\APP_DATA\SOSYM DATABASE.MDF", table "dbo.PublicationType", column 'typeID'. Cannot insert the value NULL into column 'publicationID', table 'C:\INETPUB\WWWROOT\SOSYM WEBSITE\APP_DATA\SOSYM DATABASE.MDF.dbo.PublicationAuthors'; column does not allow nulls. INSERT fails

but i shouldnt be referencing the typeID from the PublicationType Table this doesnt get inserted into the PublicaitonAuthors table at all.

|||

Ok, but the good news is that the stored proc is called correctly

The insert probably failed because publicationId wasn't defined as an identity column, and it wasn't part of the insert statement.

|||

but the publicationID isdefined as the identity column in proc one and its part of the insert statement in proc 2 ?

|||

Hi,

From the error you provided, basically it has to do with putting invalid data into a column that references the data in the primary (or unique) key of another table, or what we can say that the value you are going to insert into the foreign key field doesn't exist in the corresponding filed of the parent table.

In order to have a test, you can split your stored procedure into two parts, first to check if the inserting to the parent table can work successfully. If so, we can know is issue is related to the parameter passing between these two procs.

After that ,try the following code:

exec StoredProcedureOne @.typeId = 0, @.title='test', @.publicationIdoutput INSERT INTO PublicationAuthors (publicationID, authorID)VALUES (@.publicationID, @.authorID)SELECT @.publicationId

Thanks.