Could not find stored procedure

Question

I created a procedure to rebuild indexes with a cursor. Instead of executing properly, I get an error message:

Msg 2812, Level 16, State 62
Could not find stored procedure

What to do to get rid of this situation?

USE [ACT]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
ALTER PROCEDURE [dbo].[myProcedure]
AS
BEGIN

DECLARE @idx_name VARCHAR(200) -- Index name  
DECLARE @tableName VARCHAR(256) -- Table Name
DECLARE @sql varchar(500) 

DECLARE Index_Rebuild_Cursor CURSOR FOR  
SELECT OBJECT_NAME(ps.OBJECT_ID)tbl_name,b.name idx_name
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id 
WHERE ps.avg_fragmentation_in_percent>80

OPEN Index_Rebuild_Cursor   
FETCH NEXT FROM Index_Rebuild_Cursor INTO @tableName,@idx_name

WHILE @@FETCH_STATUS = 0   
BEGIN   

 SET @sql='ALTER INDEX '+@idx_name+' ON '+@tableName+' REBUILD'
 EXEC @sql

 FETCH NEXT FROM Index_Rebuild_Cursor INTO @tableName,@idx_name
END   

CLOSE Index_Rebuild_Cursor   
DEALLOCATE Index_Rebuild_Cursor

END

Answer 1

EXEC @sql is invalid syntax (you need EXEC(@sql); - those parentheses are important). Nonetheless, I highly recommend:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'ALTER ...';
-----------^ this N is important too
...

EXEC sp_executesql @sql;

Doing this as a habit makes sure that you will be able to use strongly typed parameters when you can.

Answer 2

search for the sp with

SELECT  *
FROM    sys.sql_modules
WHERE   definition LIKE '%SET @sql=''ALTER INDEX ''+@idx_name+'' ON ''+@tableName+'' REBUILD''%'

if it exists under a different name you might find it with this query.