thatdude thatdude - 5 months ago 12
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

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