Call stored procedure from 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

General SQL Server Forums

New to SQL Server Programming

Call stored procedure from stored procedure

Author

Topic

petebob796
Starting Member

35 Posts

Posted - 2008-02-08 : 19:11:36

I know this is probably simple but I am trying to call a stored procedure from another stored procedure. Basically this is so I can group lots of table updates into one overnight procedure to run. I don't need a record return using temp tables which is the only reference I seem to be able to find about stored procedures in stored procedures. I just want it to run. Tried the obvious:

exec [UStudent_Marks_Filtered]

Help much appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-08 : 20:01:36

What you have posted is what you would need to use if you just want it to run. But you may need to pass parameters.

Are you getting an error?

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

petebob796
Starting Member

35 Posts

Posted - 2008-02-08 : 20:18:32

The exact code I was trying is:

CREATE PROCEDURE [dbo].[USecondary Tables]
AS
BEGIN
delete from [Student Marks Filtered]
GO
exec [UStudent Marks Filtered]
GO
delete from [RegisterDetails Filtered]
GO
exec [URegisterDetails Filtered]
GO
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
delete from [Attendance Group]
go
exec [UAttendance Group]
GO
delete from [Attendance RegisterID]
go
exec [UAttendance RegisterID]
go
delete from [Attendance Session]
go
exec [UAttendance Session]
go
delete from [Attendance Static]
go
exec [UAttendance Static]
go
delete from [Attendance ProgArea]
go
exec [UAttendance ProgArea]
go
delete from [Attendance Division]
go
exec [UAttendance Division]
go
delete from [Attendance School]
go
exec [UAttendance School]
go
delete from [Attendance College]
go
exec [UAttendance College]
go
delete from [Attendance StudentOverall]
go
exec [UAttendance StudentOverall]
go
delete from [Attendance Group Filtered]
go
exec [UAttendance Group Filtered]
go
delete from [Attendance Register Filtered]
go
exec [UAttendance Register Filtered]
go
delete from [Attendance ProgArea Filtered]
go
exec [UAttendance ProgArea Filtered]
go
delete from [Attendance College Filtered]
go
exec [UAttendance College Filtered]
go
end

and the error I get is:

Msg 102, Level 15, State 1, Procedure USecondary Tables, Line 4
Incorrect syntax near 'Student Marks Filtered'.
Msg 2627, Level 14, State 1, Procedure UStudent Marks Filtered, Line 4
Violation of PRIMARY KEY constraint 'aaaaaStudent Marks Filtered_PK'. Cannot insert duplicate key in object 'dbo.Student Marks Filtered'.
The statement has been terminated.

(1486 row(s) affected)

(1486 row(s) affected)

(2478 row(s) affected)
Division by zero occurred.

(2478 row(s) affected)

(830 row(s) affected)
Division by zero occurred.

(830 row(s) affected)

(648 row(s) affected)
Division by zero occurred.

(648 row(s) affected)

(469 row(s) affected)
Division by zero occurred.

(469 row(s) affected)

(41 row(s) affected)
Division by zero occurred.

(41 row(s) affected)

(19 row(s) affected)
Division by zero occurred.

(19 row(s) affected)

(8 row(s) affected)
Division by zero occurred.

(8 row(s) affected)

(2 row(s) affected)

(2 row(s) affected)

(3408 row(s) affected)
Division by zero occurred.

(3408 row(s) affected)

(1235 row(s) affected)
Division by zero occurred.

(1235 row(s) affected)

(270 row(s) affected)
Division by zero occurred.

(270 row(s) affected)

(38 row(s) affected)
Division by zero occurred.

(38 row(s) affected)

(2 row(s) affected)

(2 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'end'.

I thought there was a problem with the delete but if I take that out it fails on the first exec line with:
Msg 102, Level 15, State 1, Procedure USecondary Tables, Line 5
Incorrect syntax near 'UStudent Marks Filtered'.

None of the stored procedures require input it's an overnight thing just clears down some tables and recreates them with the latest data. I find it a bit bizare because it seems to be running the stored procedure rather than creating it unless I have been silly it is a bit late here.

petebob796
Starting Member

35 Posts

Posted - 2008-02-08 : 20:22:51

The division by zero messages are expected just in case your wondering it's a student attendance percentage calculation and I handle it setting percentage to zero if they have never attended.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-08 : 20:52:09

Get rid of the GO statements in your stored procedure. You don't use those in stored procedures.

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

petebob796
Starting Member

35 Posts

Posted - 2008-02-08 : 20:55:23

of course thanks thought it was something silly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources