passing value of one store procedure to another SP

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

General SQL Server Forums

New to SQL Server Programming

passing value of one store procedure to another SP

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-10 : 10:26:11

hello,

I want to pass one value of stored procedure to another stored procedure.

here are the two tables and basically i am inserting elements into the table.

Table::::TOCHeaderSchema
Columns:::TOCHeaderSchemaID.....auto generated..PK
TOCHeaderSchemaNameText varchar

table::::TOCHSElement
Column:::TOCHSElementID...autogenerated.PK
TOCHSLevel
TOCHSPrefixText
TOCHSCOunterstyleID
TOCHeaderSchemaID....FK

here is my 1st stored procedure

ALTER PROCEDURE [dbo].[sp_InsertNewHeadertext]
(

@TOCHeaderSchemaNameText as varchar(200)

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @ID1 as int

Insert into TOCHeaderSchema (TOCHeaderSchemaNameText)VALUES(@TOCHeaderSchemaNameText)
SELECT @ID1 = @@IDENTITY

END

and here is my second one

ALTER PROCEDURE [dbo].[sp_InsertTOCElement]

(@TOCHSCounterStyleID as int,
@TOCHSLevel as int,
@ID1 as int,
@TOCHSPrefixText as varchar(50)

)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

EXEC sp_InsertNewHeadertext @ID1 OUTPUT

Insert into TOCHSElement(TOCHSLevel,TOCHSPrefixText,TOCHSCounterStyleID,TOCHeaderSchemaID)VALUES( @TOCHSLevel,@TOCHSPrefixText,@TOCHSCounterStyleID,@ID1)
END

I am getting error saying 'sp_InsertTOCElement' expects parameter '@ID1', which was not supplied. in my C# code

please help...I need this to be done urgently

thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-10 : 10:37:14

[code]
ALTER PROCEDURE [dbo].[sp_InsertNewHeadertext]
(

@TOCHeaderSchemaNameText as varchar(200)
,@ID1 int output
)
AS
...
GO

(in the second SP:)

declare @ID1 int
exec sp_InsertNewHeadertext @ID1 = @ID1 output
[/code]

Be One with the Optimizer
TG

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-10 : 10:41:34

Hello...Thanks for the reply

but if I am writing

ALTER PROCEDURE [dbo].[sp_InsertNewHeadertext]
(

@TOCHeaderSchemaNameText as varchar(200)
@ID1 as int OUTPUT,

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

then its saying....... Procedure or Function 'sp_InsertNewHeadertext' expects parameter '@ID1', which was not supplied.

please help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-10 : 10:42:21

sorry:

ALTER PROCEDURE [dbo].[sp_InsertNewHeadertext]
(

@TOCHeaderSchemaNameText as varchar(200)
,@ID1 int = null output
)
AS
...
GO

Be One with the Optimizer
TG

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-10 : 10:43:53

Change
SELECT @ID1 = @@IDENTITY

to
SELECT @ID1 = scope_identity()

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-10 : 10:47:11

okay this is what I have done

ALTER PROCEDURE [dbo].[sp_InsertNewHeadertext]
(

@TOCHeaderSchemaNameText as varchar(200),
@ID1 int= NULL OUTPUT

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Insert into TOCHeaderSchema (TOCHeaderSchemaNameText)VALUES(@TOCHeaderSchemaNameText)
SELECT @ID1 = scope_identity()

END

ALTER PROCEDURE [dbo].[sp_InsertTOCElement]

(@TOCHSCounterStyleID as int,
@TOCHSLevel as int,

@TOCHSPrefixText as varchar(50)

)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @ID1 as int
EXEC sp_InsertNewHeadertext @ID1 =@ID1 OUTPUT

Insert into TOCHSElement(TOCHSLevel,TOCHSPrefixText,TOCHSCounterStyleID,TOCHeaderSchemaID)VALUES( @TOCHSLevel,@TOCHSPrefixText,@TOCHSCounterStyleID,@ID1)
END

but still I am getting error

saying Procedure or function sp_InsertTOCElement has too many arguments specified.

please help

thank you

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-10 : 10:51:48

Actually value is getting inserted in one table TOCHeaderSchema,but not in TOCHSElement

Please help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-10 : 11:01:30

Look at the error message: "sp_InsertTOCElement has too many arguments specified"

Post the call you are making to sp_InsertTOCElement (As the message indicates you are passing in more arguments than is defined)

Be One with the Optimizer
TG

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-10 : 11:04:07

okay here is what I have done in my C# code...
protected void Button1_Click(object sender, EventArgs e) {

int schemaid;

schemaid = tocheaderstyle.insertheaderschema(txtheaderschema.Text);

//loop 6 times
tocheaderstyle.inserttocelement(1, txtprefix1.Text, System.Convert.ToInt32(DDL1.SelectedValue), schemaid);
tocheaderstyle.inserttocelement(2, txtprefix2.Text , System.Convert.ToInt32(DDL2.SelectedValue), schemaid);
tocheaderstyle.inserttocelement(3, txtprefix3.Text , System.Convert.ToInt32(DDL3.SelectedValue), schemaid);
tocheaderstyle.inserttocelement(4, txtprefix4.Text , System.Convert.ToInt32(DDL4.SelectedValue), schemaid);
tocheaderstyle.inserttocelement(5, txtprefix5.Text , System.Convert.ToInt32(DDL5.SelectedValue), schemaid);
tocheaderstyle.inserttocelement(6, txtprefix6.Text , System.Convert.ToInt32(DDL6.SelectedValue), schemaid);

}

For perticular TOCHeaderSchemaNameText,I have to insert 6 levels,and its corresponding prefix and type.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-10 : 11:19:49

Before you try all this from your c# code make sure it works from a query window.

One thing I see is that it looks like you are missing a parameter in this call:

EXEC sp_InsertNewHeadertext @TOCHeaderSchemaNameText = <?>, @ID1 =@ID1 OUTPUT

Then you should make sure that this call works (from a Query Window):


exec sp_InsertTOCElement
 @TOCHSCounterStyleID = <SomeInt>,
 @TOCHSLevel <SomeInt>,
 @TOCHSPrefixText = '<someText>'

How does sp_InsertTOCElement know what value to use for @TOCHeaderSchemaNameText when it calls sp_InsertNewHeadertext

Be One with the Optimizer
TG

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-10 : 11:39:46

In this case what shall I do,can you guide me.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-10 : 13:00:57

One simple and probably more standard way to populate parent/child tables (one parent, one or more children) is to first make a call to the Parent insert SP and get the identity as an output variable. Then make subsequent calls to the child insert SP to insert the children (one call at a time).

I'm not sure what method you intended to use here. It looks like maybe you wanted to make a direct call to the child table insert SP and let it handle populating the parent. One thing I don't see is the conditional logic necessary to only populate the parent row if it hasn't already been populated. ie: the first call to the child SP would first populate the parent table. The subsequent calls would only populate the child table.

Using this basic design description (as I've done above) why don't you first specifiy what you want to do. If you need help doing it then I (or someone) will be able to guide you.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources