如何保存存储过程？

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.

现在，我想知道如何在存储过程文件夹（Programmability - > Stored Procedure）下保存我的SPROC，以便我可以通过名称（最终来自我的C＃应用程序）访问它。到目前为止，当我按下保存图标时，我建议保存整个查询。那不是我想要的。

Thanks for helping

谢谢你的帮助

4 个解决方案

#1

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

实际上，您必须运行CREATE PROCEDURE命令以便构建存储过程。

Create Procedure - MSDN

创建过程 - MSDN

Here's an example straight from the MSDN page:

以下是直接来自MSDN页面的示例：

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.

我还建议在某处保存* .sql文件，以便在需要再次运行的情况下将CREATE PROCEDURE脚本放在某处。

#2

you have to actually run the create proc statement.

你必须实际运行create proc语句。

#3

Based on the OP's comment:

基于OP的评论：

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?

我使用EXEC命令运行它，它工作。当我在Stored Procedure文件夹下没有看到SPROC时，我虽然没有创建它。所以我试着再次创建它，但我被告知已经存在具有相同名称的对象。我想我能够创造它。但是，为什么它在Programmability - > Stored Procedure文件夹下不可见？

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.

当您第一次运行存储过程CREATE PROCEDURE ....时，您可能需要刷新SSMS中的存储过程列表。右键单击然后刷新。

#4

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

您需要运行CREATE PROCEDURE命令。尝试使用这个简单的存储过程示例：

CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate
GO

#1