Could not find stored procedure ' '. SQL Server 2008

Question

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?

is that your actual code ? I can't reproduce what you are asking. Too vague — t-clausen.dk
Have you tried to debug this by printing out the value of @SQL by adding the statement PRINT @SQL or SELECT @SQL? It sounds like the value of @SQL might not be what you expected. — Adam Porad
@Adam while possible, the current syntax being used EXEC @sql; vs EXEC(@sql); will still not work, regardless of what string is in @sql. — Aaron Bertrand
@AaronBertrand I was able to get EXEC @sql to work when the value of @sql was set to the stored procedure name without parameters. For example: `set @sql = 'sp_help'. Here's an example on SQLFiddle — Adam Porad

Aaron Bertrand Aaron Bertrand · Accepted Answer · 2013-11-06T18:46:50

Make sure @sql is NVARCHAR.
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);
```

Could not find stored procedure ' '. SQL Server 2008

1 Answers