Warning: Could Not Find Stored Procedure

Apply in :
Microsoft SQL Server 2008 R2

Solution:

* * * * * 

1.
Example Data:
Try to run this query
------------------------------------------------------------------------------
------------------------------------------------------------------------------
CREATE TABLE [dbo].[Sample_Table_1]
(
[valueid] [varchar](15) NOT NULL,
[description] [varchar](255) NULL
)
------------------------------------------------------------------------------
------------------------------------------------------------------------------



2.
Example Data: Insert dummy data.
------------------------------------------------------------------------------
------------------------------------------------------------------------------
INSERT INTO Sample_Table_1 VALUES ('GRAND MAX','Grand Max Car')
INSERT INTO Sample_Table_1 VALUES ('1.3 D FF','Isuzu Panther')
INSERT INTO Sample_Table_1 VALUES ('2.5 G DIESEL AT','Toyota Avanza')
------------------------------------------------------------------------------
------------------------------------------------------------------------------


3.
When try to run this query :
------------------------------------------------------------------------------
------------------------------------------------------------------------------
CREATE Procedure [Car_List]
@ValueID varchar(20)
as
begin

Declare @strSQL varchar (1000)

set @strSQL=''

set @strSQL = @strSQL +
' select valueID, Description
from Sample_Table_1
'

execute @strSQL
end
------------------------------------------------------------------------------
------------------------------------------------------------------------------
Result Messages:
Command(s) completed successfully.


4.
Then, try to run the procedure, show an warning/error below
------------------------------------------------------------------------------
------------------------------------------------------------------------------
EXEC [Car_List] ''
------------------------------------------------------------------------------
------------------------------------------------------------------------------

Result Messages:
Msg 2812, Level 16, State 62, Procedure Car_List, Line 15

Could not find stored procedure

' select valueID, Description
from Sample_Table_1
'.



5. Solution: 

Fix the query in line of ( "execute @strSQL" ) become ( "execute (@strSQL) " ).
This need a pair of "(" and ")".

Next, try this fix of query
------------------------------------------------------------------------------
------------------------------------------------------------------------------
ALTER Procedure [Car_List]
@ValueID varchar(20)
as
begin

Declare @strSQL varchar (1000)

set @strSQL=''

set @strSQL = @strSQL +
' select valueID, Description
from Sample_Table_1
'

execute (@strSQL)

end
------------------------------------------------------------------------------
------------------------------------------------------------------------------

Then, run the procedure again,
------------------------------------------------------------------------------
------------------------------------------------------------------------------
EXEC [Car_List] ''
------------------------------------------------------------------------------
------------------------------------------------------------------------------
Result Messages:
valueID                          Description
---------------                 ----------------
GRAND MAX       Grand Max Car
1.3 D FF                         Isuzu Panther
2.5 G DIESEL               AT Toyota Avanza



6. Optional
To remove sample table dan dummy data
------------------------------------------------------------------------------
------------------------------------------------------------------------------
DROP TABLE Sample_Table_1
DROP PROCEDURE Car_List
------------------------------------------------------------------------------
------------------------------------------------------------------------------