Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created a table testtable inside the database testbase that have the following structure:

product_no (int, not null)
product_name (varchar(30), not null)
price (money, null)
expire_date (date, null)
expire_time (time(7), null)

which I used the Microsoft SQL Server 2008 Management Studio.

I created a stored procedure testtable_pricesmaller as follows

use testbase
go
create procedure testtable_pricesmaller
    @pricelimit money
as
select * from testtable where price = @pricelimit;
go

and are able to view the Stored Procedures on the Object Explorer of the Microsoft SQL Server Management Studio. (It is listed in the following tree structure of the Object Explorer)

Databases
    + testbase
        + Tables
            + dbo.testtable
        + Programmability
            + Stored Procedures
                + dbo.testtable_pricesmaller

I find it very strange when I receive the following error:

Could not find the stored procedure 'dbo.testtable_pricesmaller'.

when I execute the following SQL statement:

execute dbo.testtable_pricesmaller 50

What could it be missing?

share|improve this question
    
Did you make sure your execute statement is running under the context of "testbase" database? You might try adding a USE statement or fully qualifying the object name. –  Shawn Melton Sep 10 '12 at 5:24
    
Yes, I did add the USE statement but it give me the error. –  Jack Sep 10 '12 at 5:37
add comment

3 Answers 3

up vote 3 down vote accepted

At last I know why the message appear in the MS SQL Server Management Studio.

The MS SQL Server Management Studio require one to restart it after creating a stored procedure in it.

After restarting the MS SQL Server Management Studio, there is no such error anymore.

(Strange, does that mean that every time I create a stored procedure, I have to restart it?)

share|improve this answer
7  
You should not have to restart it. –  Shawn Melton Sep 10 '12 at 8:55
1  
@ShawnMelton I mean that I close the MS SQL Server Management Studio and re-open the MS SQL Server Management Studio. I just find it quite strange that I have to close and then re-open it. Is there any configuration of the MS SQL Server Management Studio (SSMS) that I might be missing, resulting the SSMS unable to reflect that the Stored Procedure is already created? –  Jack Sep 10 '12 at 9:15
3  
IntelliSense is the only lagging portion of what you did. Executing the exec statement should have worked without restarting SSMS. There is something else that contributed to it not working. I agree with @ShawnMelton. –  Thomas Stringer Sep 10 '12 at 16:03
    
@Shark, Interesting! I would appreciate if anyone can explain to me that why my SSMS behave in such way that I find it quite strange. –  Jack Sep 11 '12 at 9:55
add comment

You should not have to restart the database after added a new stored procedure. Although you will need to refresh your object explorer to see it there. The next time you add a stored procedure try running the right click execute option from the object explorer and enter your parameters and see if it runs. If it does not run then I'm not sure what your problem is. If it does run then it could be something simple like SQL is trying to query from the wrong database.

share|improve this answer
add comment

Your create command should be

create procedure dbo.testtable_pricesmaller
    @pricelimit money

you are missing dbo. before procedure name. Whenever you create a procedure, it is good practice to explicitly define the user/schema with the name of a procedure i.e. procedure name should have fully qualified signatures.

I hope this will help you.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.