Incorrect syntax in stored procedure that calls another?

[solla]

I had 2 queries defined in an Access database:

==================================
SELECT UM_Groups_T.*
FROM UM_Groups_T
WHERE (UM_Groups_T.AlertGroup=True)
ORDER BY UM_Groups_T.Name;
==================================
SELECT COUNT(*) AS AlertGroupsCount
FROM UM_AlertGroupsList_Q;
==================================

i.e. the latter called the former.


I needed to recreate these in an SQL Server database, so created the following stored procedures:

=========================================
CREATE PROCEDURE UM_AlertGroupsList_Q

AS (SELECT TOP 100 PERCENT UM_Groups_T.*
FROM UM_Groups_T
WHERE (UM_Groups_T.AlertGroup=1))
ORDER BY UM_Groups_T.Name
GO
=========================================
CREATE PROCEDURE UM_AlertGroupsCount_Q (@reqGroupId int
)

AS
SELECT COUNT(*) AS AlertGroupsCount
FROM UM_AlertGroupsList_Q
GO
==========================================


This passes the syntax test, but when I try to execute this stored procedure, I get the following message:

Server: Msg 208, Level 16, State 3, Procedure UM_AlertGroupsCount_Q, Line 5
Invalid object name 'UM_AlertGroupsList_Q'.
Stored Procedure: MyDB.dbo.UM_AlertGroupsCount_Q

Is this just a syntactic error?

 

[SQLSister]

You can't call a stored propcedure from another stored procedure that way. You need to return the results to a temp table or table variable and then use that. But I'd don't thin you need two stored procedures at all.
Try this instead:

SELECT COUNT(*) AS AlertGroupsCount
FROM UM_Groups_T
WHERE UM_Groups_T.AlertGroup=1

That should get the count of the Alert groups with a value of 1.

 

[solla]

Yeah, you're right. What I'd done was almost a blind copy of the Access syntax. Thanks very much.