如何将T-SQL查询转换为存储过程?

I am running SQL Server 2012 and I need to convert a T-SQL query into a Stored Procedure. My aim is to run a SQL job that will execute this Stored Procedure on a daily basis.

我正在运行SQL Server 2012，我需要将T-SQL查询转换成存储过程。我的目标是运行一个SQL作业，它将每天执行这个存储过程。

My T-SQL query stands as follows:

我的T-SQL查询如下:

DECLARE @Body NVARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)

SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
    + '</style>' + '</head>' + '<body>' + 'Report generated on : '
    + CONVERT(VARCHAR(50), GETDATE(), 106) 
    + ' <br> <table cellpadding=0 cellspacing=0 border=0>' 
    + '<tr> <td bgcolor=#E6E6FA><b>StayYear</b></td>'
    + '<td bgcolor=#E6E6FA><b>Market</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jan</b></td>'
    + '<td bgcolor=#E6E6FA><b>Feb</b></td>'
    + '<td bgcolor=#E6E6FA><b>Mar</b></td>'
    + '<td bgcolor=#E6E6FA><b>Apr</b></td>'
    + '<td bgcolor=#E6E6FA><b>May</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jun</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jul</b></td>'
    + '<td bgcolor=#E6E6FA><b>Aug</b></td>'
    + '<td bgcolor=#E6E6FA><b>Sep</b></td>'
    + '<td bgcolor=#E6E6FA><b>Oct</b></td>'
    + '<td bgcolor=#E6E6FA><b>Nov</b></td>'
    + '<td bgcolor=#E6E6FA><b>Dec</b></td>';

SET @Body = ( SELECT *
              FROM  View1               
                FOR   XML RAW('tr'),
                      ELEMENTS
            )


SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

What are the steps to convert the above into a Stored Procedure?

将上述转换为存储过程的步骤是什么?

2 个解决方案

#1

You Can add make your script as a Stored Procedure by simply binding your Query inside a Create Procedure statement. Like this

只需将查询绑定到Create Procedure语句中，就可以将make脚本添加为存储过程。像这样

CREATE PROCEDURE <Database Schema>.<Procedue Name>
AS
BEGIN
   <Your Script>
END

you can copy and paste your Query between the BEGIN and END.

您可以在开始和结束之间复制和粘贴您的查询。

Like this

像这样

CREATE PROCEDURE dbo.sProc_MyProc
AS
BEGIN

DECLARE @Body NVARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)

SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
    + '</style>' + '</head>' + '<body>' + 'Report generated on : '
    + CONVERT(VARCHAR(50), GETDATE(), 106) 
    + ' <br> <table cellpadding=0 cellspacing=0 border=0>' 
    + '<tr> <td bgcolor=#E6E6FA><b>StayYear</b></td>'
    + '<td bgcolor=#E6E6FA><b>Market</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jan</b></td>'
    + '<td bgcolor=#E6E6FA><b>Feb</b></td>'
    + '<td bgcolor=#E6E6FA><b>Mar</b></td>'
    + '<td bgcolor=#E6E6FA><b>Apr</b></td>'
    + '<td bgcolor=#E6E6FA><b>May</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jun</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jul</b></td>'
    + '<td bgcolor=#E6E6FA><b>Aug</b></td>'
    + '<td bgcolor=#E6E6FA><b>Sep</b></td>'
    + '<td bgcolor=#E6E6FA><b>Oct</b></td>'
    + '<td bgcolor=#E6E6FA><b>Nov</b></td>'
    + '<td bgcolor=#E6E6FA><b>Dec</b></td>';

SET @Body = ( SELECT *
              FROM  View1               
                FOR   XML RAW('tr'),
                      ELEMENTS
            )


SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

END

#2

Simply right-click 'Stored Procedures' and click 'Stored Procedure'.

只需右键单击“存储过程”并单击“存储过程”。

A new window will open with some very basic code.

一个新窗口将用一些非常基本的代码打开。
Delete the stuff in green

删除绿色的东西
Name your Stored Procedure, with no spaces in the name of the SProc.

命名您的存储过程，没有空格以SProc的名称。
Paste your SQL script in between the BEGIN and END clauses.

将SQL脚本粘贴到开始和结束子句之间。
Hit F5 to compile your Sproc.

按F5编译Sproc。
If everything is setup correctly, you will get a message that reads

如果一切设置正确，您将得到一条读取的消息

Command(s) completed successfully.

命令已成功完成。

#1