Stored Procedure in 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

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure in another SP

Author

Topic

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2009-08-14 : 12:38:33

I'm trying to call a stored procedure within a stored procedure, but I keep getting a error stating "Incorrect syntax near '@caseobjid'" It's identifying the line where the SP is being called?

Can a SP be called within an SP? I thought it could.

Any help? Here's the code:

--====================================
CREATE PROCEDURE usp_sp1
@caseobjid int --THIS IS THE OBJID ID FROM THE TABLE_CASE TABLE
AS

DECLARE @ERROR INT

BEGIN TRANSACTION
INSERT INTO [server].[database].[DBO].[Table] (columns1, columns2)

SELECT columns1, columns2
FROM [server2].[database2].[DBO].[Table2]
WHERE case = @caseobjid

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

BEGIN TRANSACTION
usp_archive_primcon_delete @caseobjid
IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 14:10:06

CREATE PROCEDURE usp_sp1
@caseobjid int --THIS IS THE OBJID ID FROM THE TABLE_CASE TABLE
AS

DECLARE @ERROR INT

BEGIN TRANSACTION
INSERT INTO [server].[database].[DBO].[Table] (columns1, columns2)

SELECT columns1, columns2
FROM [server2].[database2].[DBO].[Table2]
WHERE case = @caseobjid

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

BEGIN TRANSACTION
EXEC usp_archive_primcon_delete @caseobjid
IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 14:10:54

why the 2 seperate transactions? seems unnecessary to me

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2009-08-14 : 15:00:04

I did have it under one sp, but what I'm trying to do did not work. So I've been testing it out by breaking it apart.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources