How to save a Stored Procedure ?

Question

Hello,

I've been playing today with stored procedures. I'm stoned 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 have to actually run the create proc statement.

Answer 2

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.

Answer 3

You need to run CREATE PROCEDURE command.
Try with this simple Stored Procedure example:

CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate
GO

Answer 4

Based on the OP's comment:

I've run it using EXEC command, and it worked. When I didn't see the SPROC under the Stored Procedure folder, I though I didn't create it. So I tried to create it again, but I was told that the object with the same name exists already. I guess I was able to create it. But, why it is not visible under the Programmability --> Stored Procedure folder?

When you run the stored procedure CREATE PROCEDURE.... for the first time, you may need to refresh your stored procedure list in SSMS. Right click then REFRESH.