Help with Transaction and Alter Procedure SQL

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)

Help with Transaction and Alter Procedure SQL

Author

Topic

roger_james_jr
Starting Member

7 Posts

Posted - 2004-09-30 : 21:23:07

Hi,

Can someone please help me with this. I am not sure why it is failing, or how to fix it.

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'PROCEDURE'.
Server: Msg 137, Level 15, State 1, Line 14
Must declare the variable '@mySomething'.

BEGIN TRANSACTION myTransaction

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[mytable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
ALTER PROCEDURE myProcedure
(
@myID INT,
@mySomething VARCHAR(255)
)
AS

SELECT
@mySomething + myFieldValue AS myDesiredResult
FROM
myTable

END

COMMIT TRANSACTION myTransaction

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-01 : 02:11:41

the following should work

BEGIN TRANSACTION myTransaction

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE myProcedure
@myID INT,
@mySomething VARCHAR(255)
AS

SELECT
@mySomething + b AS myDesiredResult
FROM
table1

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

COMMIT TRANSACTION myTransaction

but why r u cheking on the existence of the table in order to alter a procedure ? shouldn;t u check the existence of the procedure ? ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources