CODEWITHSUNDEEP
sql-server-2008t-sqldynamic-sql
codingManiac
codingManiac

Reputation: 1700

Could not find stored procedure ' '. SQL Server 2008

I have created a stored procedure and am receiving the following error when trying to call it using an exec 'procedure_name' statement. The procedure has a chunk of static SQL code in the first half and then dynamic code for the last half...

calcNums(Param1, Param2, etc...)
--------
DECLARE a bunch of variables
--------

STATIC CODE here

-------
SET @SQL = DYNAMIC CODE

EXEC @SQL

I have the right database connection selected and I created the procedure with the dbo. schema prefix.

If I call the procedure without any parameters then I get an error of...

Procedure or function 'calcNums' expects parameter 'Param1' which was not supplied.

Otherwise it gives me the error...

Could not find stored procedure ' '.

Can anyone give me an idea as to why I am getting this error?

Upvotes: 1

Views: 8986

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

  1. Make sure @sql is NVARCHAR.

  2. You can't say EXEC @sql. You should use:

    EXEC sp_executesql @sql;

    If @sql is just a procedure name, then being more explicit is probably better:

    SET @sql = N'EXEC ' + @sql;
EXEC sp_executesql @sql;

    The lazy workaround is to use the following, but I think the above is better, especially if some of your parameters can bas passed in explicitly:

    EXEC(@sql);

Upvotes: 5

Related Questions