When executing a dynamic SQL statement within a stored procedure and you get a "Stored Procedure not found" error at the line where you have an SQL statement, chances are is that you don't have brackets around your variable. Let me explain with an example:

CREATE PROCEDURE usp_get_authors
 @SortDirection VARCHAR (4)
AS
DECLARE @SQL VARCHAR(4000)

SELECT @SQL = 'SELECT * FROM authors ORDER BY name ' + @SortDescription

EXEC @SQL -- This will give a Stored Procedure not found error

EXEC(@SQL) -- This is what you really want.

So while it is a subtle difference the first statement actually tries to execute a stored procedure named, whilst the second statement executes as an SQL string.

Technorati Tags: SQL, T-SQL EXEC Statement