Stored Procedure Problem

[Marmalade]

Hi,

I'm writing a stored procedure to
(1) Create a new record in one table (A) and store its identity value;
(2) then create a new record in a second table (B) and store *its* identity value;
(3) then create a new record in a third table (C) and populate it using the identity values generated in the first two steps.

I've included the basic code below.

Every time I run the procedure, a new record is created an populated in Table A; but I then get the following error message:
Server: Msg 208, Level 16, State 3, Procedure spInsertItem, Line 36 Invalid object name 'TableB'.

Can anyone tell me why this is happening?

USE Database
GO
CREATE PROC spInsertItem
@VariableA1,
@VariableA2,
@VariableB1,
@VariableB2

AS

/* Create and populate new record in TableA*/

INSERT INTO TableA
VALUES
(
@VariableA1,
@VariableA2
)

/* Move the identity value of the new TableA record into a  variable */
DECLARE @IdentityA int
SET  @IdentityA = @@IDENTITY

/* Create and populate new record in TableB*/

INSERT INTO B
VALUES
(
@VariableB1,
@VariableB2
)

/* Move the identity value of the new TableB record into a  variable */
DECLARE @IdentityB int
SET  @IdentityB = @@IDENTITY

/* Create and populate new TableC record */

INSERT INTO TableC
VALUES
(
(@IdentityA, 
@IdentityB
)

GO

 

[MeanGreen]

Try this. Move your declares for the @IdentityA and @IdentityB to the top of the stored procedure. When you declare the variables, sometimes you loose the @@Identity value. Also, you might list the columns after the insert command. Only other problem is tableB is called B only in your stored procedure, so you may have the tablename wrong. Example:

USE Database
GO
CREATE PROC spInsertItem
@VariableA1,
@VariableA2,
@VariableB1,
@VariableB2

AS

DECLARE @IdentityA int
DECLARE @IdentityB int

/* Create and populate new record in TableA*/

INSERT INTO TableA (col1, col2)
VALUES
(
@VariableA1,
@VariableA2
)

/* Move the identity value of the new TableA record into a variable */
SET @IdentityA = @@IDENTITY

/* Create and populate new record in TableB*/

INSERT INTO TABLEB (col1, col2)
VALUES
(
@VariableB1,
@VariableB2
)

/* Move the identity value of the new TableB record into a variable */
SET @IdentityB = @@IDENTITY

/* Create and populate new TableC record */

INSERT INTO TableC (col1, col2)
VALUES
(
(@IdentityA,
@IdentityB
)

GO

Hope this helps.

 

[cdukes]

try changing the table name from B to TableB:

INSERT INTO TableB
VALUES
(
@VariableB1,
@VariableB2
)

NOT
INSERT INTO B
VALUES
(
@VariableB1,
@VariableB2
)

Hope this helps,

Chris Dukes

 

[Marmalade]

Thanks, guys. I've done as you recommend, but I keep running into the same problem - TableB keeps raising an "invalid object" error. I've included the actual code below, using the real table and variable names (TableA = Items; TableB = CourseTerms; TableC = CourseTermItems); am I still missing something?

USE Textbooks
GO
CREATE PROC spInsertItem
@Title varchar(50),
@Author varchar (50) = NULL,
@Edition tinyint = NULL,
@PublisherID tinyint = NULL,
@Copyright smallint = NULL,
@ISBN varchar(10) = NULL,
@TypeID tinyint = NULL,
@Price money = NULL,
@CourseID smallint = 00,
@TermID tinyint = 00

AS

DECLARE @ItemID int
DECLARE @CourseTermID smallint

/* Create and populate new Item record */

INSERT INTO Items
VALUES
(
@TypeID,
@PublisherID,
@Title,
@Author,
@Edition,
@Copyright,
@ISBN,
@Price
)

/* Move the identity value of the new record into a  variable */
SET  @ItemID = @@IDENTITY

/* Create and populate new CourseTerm record */

INSERT INTO CourseTerms
(
@CourseID, 
@TermID
)
VALUES
(
@CourseID,
@TermID
)
/* Move the identity value of the new record into a  variable */
SET  @CourseTermID = @@IDENTITY

/* Create and populate new CourseTermItem record */

INSERT INTO CourseTermItems
(
@ItemID, 
@CourseTermID
)
VALUES
(
@ItemID, 
@CourseTermID
)
GO

 

[MeanGreen]

I think I see the problem.

USE Textbooks
GO
CREATE PROC spInsertItem
@Title varchar(50),
@Author varchar (50) = NULL,
@Edition tinyint = NULL,
@PublisherID tinyint = NULL,
@Copyright smallint = NULL,
@ISBN varchar(10) = NULL,
@TypeID tinyint = NULL,
@Price money = NULL,
@CourseID smallint = 00,
@TermID tinyint = 00

AS

DECLARE @ItemID int
DECLARE @CourseTermID smallint

/* Create and populate new Item record */

INSERT INTO Items
VALUES
(
@TypeID,
@PublisherID,
@Title,
@Author,
@Edition,
@Copyright,
@ISBN,
@Price
)

/* Move the identity value of the new record into a variable */
SET @ItemID = @@IDENTITY

/* Create and populate new CourseTerm record */
REMOVE THE @ for column names
INSERT INTO CourseTerms
(CourseID,TermID
)
VALUES
(
@CourseID,
@TermID
)
/* Move the identity value of the new record into a variable */
SET @CourseTermID = @@IDENTITY

/* Create and populate new CourseTermItem record */
And the same here.. Remove @ in front of column names
INSERT INTO CourseTermItems
(ItemID,CourseTermID
)
VALUES
(
@ItemID,
@CourseTermID
)
GO


That should do it!

 

[Marmalade]

Oh, man! I can't believe how stupid I was...

Thank you!