How to save a Stored Procedure?

Question

I've been playing today with stored procedures. I'm stoked that I picked the basic of it up so easily. (I'll also try triggers very soon).

Now, I'd like to know how to save my SPROC under the stored procedure folder (Programmability -> Stored Procedure) so that I can access it by name (eventually from my C# application). So far, when I press the save icon, I'm proposed to save the whole query. That's not what I want.

Thanks for helping

Answer 1

You actually have to run the CREATE PROCEDURE command so that the Stored Procedure builds.

Create Procedure - MSDN

Here's an example straight from the MSDN page:

USE AdventureWorks;
GO

-- If procedure exists already, drop it
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO

-- Create (or Re-create) the procedure
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

Remember that after you create the Stored Procedure, you will have to Right Click -> Refresh the Stored Procedure folder for the new procedure to appear.

I would also suggest saving the *.sql file somewhere so you have the CREATE PROCEDURE script somewhere in case you need to run again.