Help With 1st Stored Procedure

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 2008 Forums

Transact-SQL (2008)

Help With 1st Stored Procedure

Author

Topic

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2014-04-04 : 12:38:00

Hello, Please be easy on me. This is my 1st atored procedure. When I attempt to create the procedure below i get a message 'GQT_Client_Piv_BU' is already in use as an object name and would cause a duplicate that is not permitted

CREATE PROCEDURE dbo.uspGQT_Client_Piv
AS
SET NOCOUNT ON;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Client_Piv_BU]') AND type in (N'U'))
DROP TABLE dbo.Client_Piv_BU;

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Client_Piv]') AND type in (N'U'))
EXEC sp_rename 'Client_Piv', 'Client_Piv_BU';

GO

I'm not sure why this does not work.
Thank you so much!

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2014-04-04 : 12:39:51

CORRECTION TO 1st POST
Hello, Please be easy on me. This is my 1st atored procedure. When I attempt to create the procedure below i get a message 'Client_Piv_BU' is already in use as an object name and would cause a duplicate that is not permitted

CREATE PROCEDURE dbo.uspGQT_Client_Piv
AS
SET NOCOUNT ON;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Client_Piv_BU]') AND type in (N'U'))
DROP TABLE dbo.Client_Piv_BU;

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Client_Piv]') AND type in (N'U'))
EXEC sp_rename 'Client_Piv', 'Client_Piv_BU';

GO

I'm not sure why this does not work.
Thank you so much!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-04 : 19:19:22

I don't see the name "GQT_Client_Piv_BU" being used anywhere in the code.

However, on a more general note, you cannot have a GO inside a stored proc. Therefore, the "GO" ended the proc, and the other code just ran by itself, after the proc was created.

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2014-04-14 : 11:46:47

thank you so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources