store procedure .....help - SQL Server Forums

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

store procedure .....help

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-09 : 12:26:24

Hello,

I have 2 tables

table 1:::TOCHeaderSchema
Columns::::TOCHeaderSchemaID int
TOCHeaderSchemaNameText varchar(200)
CreatedbyLID int

table 2:::TOCHSElement
columns::::TOCHSElementID
TOCHSLevel
TOCHSPrefixText
TOCHSCounterStyleID
TOCHSHeaderSchemaID

I have to write a stored procedure,such that it will insert elements in this 2 tables.

please help me how to do this.

the following I did, and I am getting error

ALTER PROCEDURE [dbo].[sp_InsertNewTOCHSElement]
(

@TOCHSElementID as int,
@TOCHSCounterStyleID as int,
@TOCHSLevel as int,
@TOCHeaderSchemaID as int,
@TOCHSPrefixText as varchar(50),

@TOCHeaderSchemaNameText as varchar(200),
@CreatedByLID as int
)
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 (TOCHeaderSchemaID,TOCHeaderSchemaNameText,CreatedByLID)VALUES(@TOCHeaderSchemaID,@TOCHeaderSchemaNameText,@CreatedByLID)
SELECT @ID1 = @@IDENTITY

Insert into TOCHSElement(TOCHSElementID,TOCHSLevel,TOCHSPrefixText,TOCHeaderSchemaID)VALUES( @TOCHSElementID,@TOCHSLevel,@TOCHSPrefixText,)

END

ERROR

Msg 515, Level 16, State 2, Procedure sp_InsertNewTOCHSElement, Line 30
Cannot insert the value NULL into column 'TOCHeaderSchemaID', table 'charlotte.dbo.TOCHSElement'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(1 row(s) affected)

the elements are getting inserted in table TOCHeaderSchema,but not in TOCHSElement

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-09 : 12:29:34

sorry there is one error in insert statement

this is the correct one

Insert into TOCHeaderSchema (TOCHeaderSchemaID,TOCHeaderSchemaNameText,CreatedByLID)VALUES(@TOCHeaderSchemaID,@TOCHeaderSchemaNameText,@CreatedByLID)

SELECT @ID1 = @@IDENTITY

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

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-09-09 : 13:09:00

@TOCHeaderSchemaID is going as null as your error message says. Give it some value and run your sp.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-09 : 13:12:56

It would appear that either your @ID1 value is not being assigned. Are you sure that the table [TOCHeaderSchema] has an identity column?

If it does then perhaps there is a trigger on the table which is messing up the results of your @@identity call. Use scope_identity() instead.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources