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
2
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
1
-
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
andEND
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
2
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
1
-
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
andEND
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.
命令已成功完成。