proper Save location for SQL stored procedures

[thedougster]

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?

 

[gmmastros]

Stored procedures NEED to be saved (created) in the database. You can also save the stored procedure in a .sql file on your hard drive, but this is optional. You can even go one step further and put the .sql file in to a source code control system.

But... you need to create the stored procedure in the database, or else you will not be able to use it. Here's what you do....

Let me help you write your first stored procedure.

Open SQL Server Management Studio
Open a new query window
Write your stored procedure.

Create Procedure MyFirstProcedure
As
Set NoCount On
Select 'Hello World' as RandomNumber

Press F5 to create the stored procedure.

Now, you're ready to run it.

Open a new query window.

Exec MyFirstProcedure

Press F5 now, and the procedure will run, returning the 'RandomNumber'.

Now, if you want to see where the stored procedure is stored....

In SSMS, click
+ to expand your database
+ for programmability
+ for Stored Procedures

Your first procedure will appear in the list.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[thedougster]

That's EXACTLY what I'm doing. When I try to create a stored procedure again, SSMS says it's already in the database. When I load it into a query window and run it, it runs. When I try to run it from another query window or from an app, it can't be found. When I look for it in the Stored Procedures folder, it's not there.

 

[danvlas]

In your query window you may need to say

Use DatabaseName
Go
Exec YourProc

I suspect your query runs against the master database





Daniel Vlas
Systems Consultant

 

[thedougster]

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.

 

[markros]

You may want to also script your stored procedures to one big .sql file and save it in VSS (or which Source Safe software you're using)

 

[sqlfable]

last note on this is if you are writing procedures to run off third party installations (databases you did not design) it is good practice to create a linked database in a sense and create the proc's there. Then use fully qualified names to reference the other db.tables...

You can get into copyright and support issues if you alter third party databases in any way. That includes object creations