proper Save location for SQL stored procedures

[BobLewiston]

I saved a few stored procedures in SQL Server Management Studio. The default Save location, which I accepted, was C:\Documents and Settings\BobLew iston\My Documents\SQL Server Management Studio\Projects . (And yes, each stored procedure was in the form of an .sql file, and I assigned each file name root to be the same as the contained procedure's name.) But this Projects folder must be the wrong place, because my apps aren't finding these stored procedures.

I see a lot of .sql files in various places on my hard drive, but I can't find any by the names of the stored procedures listed in Management Studio's Stored Procedures folder (none of which I wrote). For that matter, I'm not even finding the Stored Procedures folder on my hard drive.

Where are stored procedures SUPPOSED to be saved?

[ck9663]

The stored proc that you created (typed in a text editor) can be saved anywhere you want. It's just an ordinary ASCII text file that you can store anywhere. So it'll be up to you to organize your files. If you need to create them, you can just open it via an editor, paste to a query window and execute.

Remember, once the stored is already created, you don't need to recreate it every time if there are no other changes.


--- CK

[BobLewiston]

ck9663:

I know they can be stored anywhere. But where do I put them so that my apps will find them?

[Uncle Dickie]

Once you have CREATEd a stored procedure it becomes part of the database.

You can see all the SPs related to your database in the Object Explorer (F8). Browse to your database, then Programmability , then Stored Procedures.

If the SP exists you should be able to run it again from a new query window by typing:

EXECUTE procedure(your procedure name)

[BobLewiston]

Uncle Dickie:

I'm executing these stored procedures in SQL Server Management Studio's New Query window, which should save them, but they're not winding up in Management Studio's Stored Procedures folder.

Can anybody tell me what I'm doing wrong?

[Uncle Dickie]

When you create the procedure, check which database you are connected to; when you have a query open the database it is going to run on will probably show in a drop down box in the query toolbar. If this is not the one you expect then change it there.

As a simple test you could try the following substituting the [database name] for the database you want to store the stored procedure in and 'table' with any valid table in that database:

USE [database name]
  
CREATE PROCEDURE [dbo].[myProcedure]
AS
BEGIN
        SELECT *
        FROM table
END

Once you have run that query it should give you a message such as 'Command(s) completed successfully.' but no actual result set.

If you then run:

EXECUTE procedure(dbo.myProcedure)

you should get everything from the table you selected. Failing that, I'm stumped!

[BobLewiston]

Thanks everybody, problem solved. It turns out that although I had been told to save stored procedures via Ctrl-F5, it's actually F5. Again, thanks to all.