Multiple Inserts in a Stored Procedure - Need Help

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

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple Inserts in a Stored Procedure - Need Help

Author

Topic

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-11-12 : 17:41:21

Greetings ... I'm sad.

I have a stored procedure that should work but it is bringing up an Error 137 which states I have not declared the Metatag_VID as a variable. But I have. Please see my code below and kindly help me before I lose my brain.

------------------------------------
CREATE PROCEDURE ins_metatag_index

@Site_ID Integer,
@PageTitle VarChar(255)

AS
INSERT INTO

metatag_viewpgs (
Site_ID,
PageTitle
) VALUES (
@Site_ID,
@PageTitle)

Select @@IDENTITY as 'MetaTag_VID' -------- THIS IS THE RETURN VALUE
GO

@MetaTag_VID Integer,
@URL VarChar(100),
@Description VarChar(255),
@Keywords VarChar(500),
@Classification VarChar(50),
@ResourceType VarChar(50),
@Subject VarChar(50),
@PageContent VarChar(4000),
@Date VarChar(50)
AS
INSERT INTO

metatag (
MetaTag_VID,
URL,
Description,
Keywords,
Classification,
ResourceType,
Subject,
PageContent,
Date
) VALUES (
@MetaTag_VID,
@URL,
@Description,
@Keywords,
@Classification,
@ResourceType,
@Subject,
@PageContent,
@Date
)
GO
----------------------------------

Happy Day,
Ed

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-12 : 17:45:24

Don't be sad

The problem is... GO is not actually a TSQL command. It is just a directive to Query Analyzer to split up batches.

So, when you run that create proc statment, it is creating the first part as the procedure, then trying to execute the second part in a new batch. Which won't work.

So, take out the GO from the middle of the proc and it will work!
Also, if you are calling the proc from ADO or something similar, do a SET NOCOUNT ON after the "AS" keyword. Otherwise it will return multiple recordsets because you have two statements that affect data there.

Hope that cheered you up

Damian

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-11-12 : 17:46:37

Thanks for your input. Now I get a 156 error.
Incorrect syntax near the keyword 'set'
Line 5: incorrect syntact near @site_ID
Must declare the variable @Site_ID

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-12 : 17:47:26

Actually, I could be waaaaay off.
Are you trying to have two procs in there ?

Damian

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-11-12 : 17:49:04

Regardubg Damians input, I removed the go and got a 170 error near Metatag_VID
Must declare variable @metatag_VID

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-11-12 : 17:51:22

Yes ... I am having 2 processes

1 ... to input into 2 columns in a table and pull up its id
2... to input the remainder into another table with the id pulled from 1

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-12 : 17:53:14

[code]
Select @@IDENTITY as 'MetaTag_VID' -------- THIS IS THE RETURN VALUE
GO[/code]

Should maybe be;
[code]
DECLARE @MetaTag_VID int
SET @MetaTag_VID = @@IDENTITY
[/code]

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-11-12 : 17:54:55

No Change ... same problem

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-12 : 17:55:07

Try this.

CREATE PROCEDURE ins_metatag_index

@Site_ID Integer,
@PageTitle VarChar(255),
@URL VarChar(100),
@Description VarChar(255),
@Keywords VarChar(500),
@Classification VarChar(50),
@ResourceType VarChar(50),
@Subject VarChar(50),
@PageContent VarChar(4000),
@Date VarChar(50)

AS

SET NOCOUNT ON

Declare @MetaTag_VID int

INSERT INTO metatag_viewpgs (
Site_ID,
PageTitle
) VALUES (
@Site_ID,
@PageTitle)

Set @MetaTag_VID = @@IDENTITY

INSERT INTO

metatag (
MetaTag_VID,
URL,
Description,
Keywords,
Classification,
ResourceType,
Subject,
PageContent,
Date
) VALUES (
@MetaTag_VID,
@URL,
@Description,
@Keywords,
@Classification,
@ResourceType,
@Subject,
@PageContent,
@Date
)

Select @MetaTag_VID as 'MetaTag_VID' -------- THIS IS THE RETURN VALUE

SET NOCOUNT OFF

GO

Damian

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-12 : 17:57:36

Nice Merkin.
Variables a little out of alignment there...

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-11-12 : 17:59:48

Damian ... You just made my day.

This works perfectly.

I really appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources