Where are Stored Procedures Stored?

[zyman10]

I am quite new to SQL Server and definitely new writing stored procedures.

I have written a stored procedure in the Query window in SQL Server Mangement Studio Express where I am just pulling the sales from the Sales table:

CREATE PROC GET_ALL_SALES_STATUS
(
	@Status INT
)
AS

SELECT * FROM Sales
WHERE StatusID = @Status

GO

Then I opened a new query window where I wrote:

USE myDB

EXEC GET_ALL_SALES_STATUS 2

So everything works, it looks good and is returning the right data. So I did a "Save All" and it saves all of this as a query in a folder on my computer.

My question is, where does the DB save the Stored Procedure that I created? I can't find it in any of the Folders in SQL Express Management studio. When I drill down in Databases->myDB->Programmability->Stored Procedures->System Stored Procedures ... I can't find my stored procedure...

This doesn't seem right, because if I close out of Management studio, and then open a Query window and perform the EXEC statement from above it works... So, the database is storing this "Stored Procedure" somewhere, hence I should be able to just find the StoredProcedure and edit it in management studio correct? Do I have open my query that i saved locally so I can edit the Stored Procedure???


Z

 

[gmmastros]

Databases->myDB->Programmability->Stored Procedures->[!]System Stored Procedures [!]

System stored procedures are those supplied by microsoft. You probably don't have any in your user database.

However.... if you click on Programmability and then press F5 (refresh), you will probably see your stored procedure. The list will occasionally need to be restored (especially when adding a new stored procedure).

Since you are just getting started here... make sure you try right-clicking on everything, just so you can see what functionality is available.

On a deeper level... stored procedures are stored in system tables within your database.

On a much deeper level, the execution plan for a stored procedure is stored in local memory (the procedure cache) so that when you run a stored procedure a second time, it will get the execution plan from memory (instead of a table) in an effort to speed up the performance.

Make sense?

-George

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

 

[zyman10]

Hi gmmastros,

Thanks for all the info. Yeah this all makes sense.

And the refresh worked! Thanks!

Z