Could not find stored procedure 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&gt;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
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.