Stored Procedure syntax problem

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure syntax problem

Author

Topic

jggtz
Starting Member

32 Posts

Posted - 2008-06-23 : 14:46:06

MS SQL SERVER 2005

My problem is the next :

I have a Stored Procedure that execute several tasks

If I write the SP en the following format :

quote:

USE [REPORTES]
GO
/****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Astral_sp_AcCpasArtC]
AS
BEGIN
SELECT ...
UPDATE ...
INSERT ...
END

It saves ok in Stored Procedures folder but doesn't run ok only send the message = Command(s) completed successfully.

Otherwise
If I write the SP in the following format :

quote:

USE [REPORTES]
GO
/****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Astral_sp_AcCpasArtC]
AS
GO
SELECT ...
GO
UPDATE ...
GO
INSERT ...
GO

It run ok select/update/insert records but doesn't saves in Stored Procedures folder
It only save the next part :

quote:

USE [REPORTES]
GO
/****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Astral_sp_AcCpasArtC]
AS

Also, when the SP has only one task (let's say a Select), it works ok with AS BEGIN - END format

Wich could be the problem?

Thanks

JG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 14:53:20

Do not put GO statements inside your stored procedure. That indicates the end of the batch, which is why you are only getting part of the code saved.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jggtz
Starting Member

32 Posts

Posted - 2008-06-23 : 15:00:58

Thank Tara,
But if I write the SP en the following format :

quote:

USE [REPORTES]
GO
/****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Astral_sp_AcCpasArtC]
AS
BEGIN
SELECT ...
UPDATE ...
INSERT ...
END

It saves ok in Stored Procedures folder but DOESN'T RUN ok, it doesn't select-update-insert records only send the message = "Command(s) completed successfully"

Thanks
JG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 15:03:52

That's because you haven't executed the code, all you did was "save" it.

GO does not execute code either, it just indicates the end of a batch.

To execute the stored procedure, do this in a new query window:
EXEC Astral_sp_AcCpasArtc

Then hit F5 or the green arrow to run it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jggtz
Starting Member

32 Posts

Posted - 2008-06-23 : 15:18:08

Thanks Tara
Please be patient, I'm really new with SP

Of course that I execute the SP and I mention that it saves ok to make clear that it doesn't have errors

Please reread my post and take note that the most relevant here is that if I use

quote:

USE [REPORTES]
GO
/****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Astral_sp_AcCpasArtC]
AS
BEGIN
SELECT ...
UPDATE ...
INSERT ...
END

It doesn't Execute ok, it didn't select-update-insert records, only send a message that saying "Command(s) completed successfully"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 15:20:42

You need to read my last post again. To run the select-update-insert inside your stored procedure, you must execute it properly. You can do that with EXEC statement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 16:13:08

quote:
Originally posted by jggtz

Thanks Tara
Please be patient, I'm really new with SP

Of course that I execute the SP and I mention that it saves ok to make clear that it doesn't have errors

Please reread my post and take note that the most relevant here is that if I use
quote:

USE [REPORTES]
GO
/****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Astral_sp_AcCpasArtC]
AS
BEGIN
SELECT ...
UPDATE ...
INSERT ...
END

It doesn't Execute ok, it didn't select-update-insert records, only send a message that saying "Command(s) completed successfully"

As said, you have complied the procedure. To run it use

EXEC [dbo].[Astral_sp_AcCpasArtC]

Madhivanan

Failing to plan is Planning to fail

jggtz
Starting Member

32 Posts

Posted - 2008-06-23 : 23:28:05

Tara

Madhivanan

Thank You very much for help me
I learned a little more

JG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources