By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

proper Save location for SQL stored procedures

P: 93
I saved a few stored procedures in SQL Server Management Studio. The default Save location, which I accepted, was C:\Documents and Settings\BobLewiston\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?
Apr 2 '09 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Apr 3 '09 #2

P: 93
ck9663:

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

Uncle Dickie
P: 67
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)
Apr 3 '09 #4

P: 93
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?
Apr 3 '09 #5

Uncle Dickie
P: 67
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. USE [database name]
  3.  
  4. CREATE PROCEDURE [dbo].[myProcedure]
  5. AS
  6. BEGIN
  7.         SELECT *
  8.         FROM table
  9. END
  10.  
  11.  
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:

Expand|Select|Wrap|Line Numbers
  1. EXECUTE procedure(dbo.myProcedure)
you should get everything from the table you selected. Failing that, I'm stumped!
Apr 3 '09 #6

P: 93
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.
Apr 3 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.