I have so many stored procedures that its becoming difficult to have an overview. Is there a way to create folders in the stored procedure folder (through server explorer in visual studio 2005 pro or another way?) its a ms sql 2005 database

thx

justanotherguy!

no one that knows an answer?

In SQL Server 2005 you manage your stored procedures using Projects. From the Tools menu select New Project give it a good overall name and find a safe place on your disk. It creates a 'Solution' with that name you put in the top box. You can then right click that solution in the Solution Explorer and add other 'projects' which are really just other folders near or in your solution folder. When you reopen this solution all the 'projects' you have added show nicely in the Explorer.

You can then add new scripts, drag and drop scripts across project boundaries, rename scripts or open the scripts by double clicking them.

I feel sorry for you since I was in the same boat - not discovering how the darn thing works until well into a project. After creating several 'projects' (remember these are just folders) under my solution I had to open each and everyone of my db scripts (over a hundred) using the normal right click script and choose 'Modify' and then in the File menu (last entry) you have a menu item "Move ScriptName.sql to..." and a pop up menu is shown with all your folders (projects).

It's clumsy but beats the hell out of a raw list of all your procedures once you feel your way around the thing. The absolute best and quite critical feature of it is that it allows you to search all your project (folder) files for keyword searches even names of the files. That really saved my butt when I added an entire new table for an older one. It allowed me to hone right in on all procedures that reference that old table.

Good Luck

It made me nervous at first to have stored procedures in the database and a separate copy on disk but after a while it was no big deal. I use the disk copies now to edit any changes. If I can't find one or forgot to add it to my projects I just pull it up with Modify and add it.

thanks for the reply @ mike 

I think I managed to accomplish the technique you are using. It's indeed a bit clumsy having to manually change every procedure from place. I'll see how this way of working changes the behaviour of automated processes as choosing the created procedures for data-adapters. I cant understand why there doesnt seem to be the simple possibiliy to create a folder inside of the stored procedures folder...

greetz!

justanotherguy

I agree. The Projects concept and use doesn't really effect the database directly so I don't see how it could effect any use of the database.

As I mentioned besides giving your some organization I do like how you can then do a text search on all the scripts in your project. That feature alone makes me want to use Projects but it's up to you.

Hi,

 What I ment with behaviour is for ex : the ease of adding datasets. When you want to use your own stored procedures with for ex : a datasets it gives you the list of all the stored procedures in your database. I'm sure there is a way of binding this list to another location = a folder in which you store the procedures, but I havent spent time yet on figuring that out.

Another something i was wondering about is = After I create the new project (folder) it then automaticly creates the script maps. when I right click one of my stored procedures in the server explorer i can choose to add this to the project's create scripts map. this map consists out of stored procedures that are created at runtime (the ones in create scripts do anyway). They check if the procedure exists and replace them if so. This technique is also used when creating a dataset with the option of creating stored procedures for it. The thing i was wondering about is : IF 200 employees use 1 stored procedures (with for ex an intranet website-gridview)...then the database checks 200 times if the procedure exists, deletes 200 times this procedure, has to allocate memory 200 times, write the new procedure to it 200 times and Read the script 200x and execute it 200 times....all that against (if i am not mistaking ofcourse) 1x read the procedure and execute the procedure in a simple stored procedure call.

I'm most probably overseeing alot of things and having lack of deep insight  (i'm a network admin actually)

thx again

Justanotherguy!

I'm sure I'm not following you 100% but I wouldn't disturb how SQL stores your scripts at all. You will end up with one set of scripts in the database and a separate (hopefully mirrored) set on disk.

When I go to create a new script and I have a Project full of my scripts opened I don't right click Object Explorer to create my new script I right click the project folder (Solution Explorer) and select new query. I write the query, save it to disk (in the Project) and then execute it against the database - boom, it's in the db and on hard disk.

It you create a new stored procedure in VS then it won't be in your Project but it's easy to add it to your project at a later point an it's no big deal if you leave one or two out of the project.

I understand you completely now. It is indeed a good way to store your procedures for future use in other projects. you have a good way of managing your scripts, editing and adding them to new projects. I will most certainly use this technique for the procedures that I will use in the future.

thank you for the tip! and success with developing!!

justanotherguy!