thatdude thatdude - 1 year ago 67
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.

Procedure:

USE ResourceRequest
GO

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


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]
GO

DECLARE @return_value int

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

SELECT 'Return Value' = @return_value

GO

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.

Use

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

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