thatdude thatdude - 1 year ago 50
SQL Question

"could not find stored procedure <procedure name>"

I have created a simple procedure that will give me the table entries containing a similar keyword.


USE ResourceRequest

CREATE PROCEDURE resource_lookup @ProjectName nvarchar(100)=null
FROM [dbo].Resource_Request
WHERE [Project Name] LIKE @ProjectName + '%'

I can execute the procedure but there appears to be something wrong with
"[dbo].[resource_lookup]" because it could not be found? I'm currently logged into the test server and can see the procedure in the "Stored Procedures" folder? What is causing this problem?

USE [ResourceRequest]

DECLARE @return_value int

EXEC @return_value = [dbo].[resource_lookup]
@ProjectName = N'mutually'

SELECT 'Return Value' = @return_value


Answer Source

You don't specify an explicit schema in the create so it creates it in your default schema which looks like it isn't dbo.


CREATE PROCEDURE dbo.resource_lookup @ProjectName nvarchar(100)=null
/* ... */

To ensure it is created as desired.

But it sounds like this is an intellisense warning rather than a runtime error.

So use Ctrl+Shift+R to refresh the intellisense cache so it picks up your newly added object.

Or if that doesn't work (some SSMS addins hijack this combination) do it through the menu options Edit -> IntelliSense -> Refresh Local Cache

Menu screenshot