How can I save a new Stored Procedure to the database I'm working with in Server Explorer?

Question

In Server Explorer (within Visual Studio) I can expand a database, right click on the Stored Procedures folder, and select "Add New Stored Procedure"

I can then add an SP and try to execute it. However, when I try to save the Stored Procedure (so that it will be subsequently available in the list of Stored Procedures for that database), it gives it the generic name "dbo.Procedure.sql*", even though I gave it another name, such as:

CREATE PROCEDURE [dbo].[duckbilledPlatypiOfIowa]

When the save dialog displays (wanting to save the .sql file to my Documents folder), I can rename it what I want, but it is not available in the list of Stored Procedures in the database.

How can I get it to be available there?

Note: If I right-click the Stored Procedure pane and select "copy path" (or some such), I do get what seems valid, namely:

MSSQL::/PROSQL42/PlatypusData/sa/SqlProcedure/dbo.Procedure.sql

But again, you see the generic "dbo.Procedure.sql" name. Yet no "dbo.Procedure.sql" displays in the list of Stored Procedures after saving, either.

So again, what do I need to do to save my new Stored Procedure into the database I'm working with?

do you have any dedicated suite for DB except the plugin in Visual Studio? — Gandalf the White, Jan 13 '16 at 17:25
When I was in college, I hard coded stored procedure - with triggers in SQL Server 2008 and it did work like a charm. — Gandalf the White, Jan 13 '16 at 17:25
@MikeZalansky: It had errors, so maybe that's the problem - it won't save until it runs error-free? — B. Clay Shannon-B. Crow Raven, Jan 13 '16 at 17:27
@GandalftheWhite: I wish I had Database Workbench but no, Server Explorer is all we've got. — B. Clay Shannon-B. Crow Raven, Jan 13 '16 at 17:28

score 3 · Accepted Answer · answered Jan 13 '16 at 17:26

   CREATE PROCEDURE [dbo].[duckbilledPlatypiOfIowa]

and the definition that follows is the TSQL which will 'save' it to the database.

If you run that line again, you should get an error saying that it already exists.

To verify that it does, right-click the 'Stored Procedures' node and choose 'Refresh'.

If it doesn't appear in the database you think you're working with, check the connection of the window you're in.

To be sure, add

USE [DatabaseName]
GO

Before executing your CREATE PROCEDURE [dbo].[duckbilledPlatypiOfIowa]

Cool, that worked (after it compiled, and I did the Refresh) — B. Clay Shannon-B. Crow Raven, Jan 13 '16 at 17:34

How can I save a new Stored Procedure to the database I'm working with in Server Explorer?

1 Answers1