SQL Server Template

Template for http://blog.joycode.com/ghj/archive/2009/02/16/115466.aspx SQL Server

When writing SQL scripts, there are a lot of common features. At this time, templates can bring us great convenience. The following uses the new stored procedure as an example to describe the benefits of using the template and how to maintain the template.

The following features apply to SQL Server 2005 and SQL Server 2008.

 

Benefits of templates:

In SQL Server, create a stored procedure as follows:

By default, the following SQL statement is obtained.

-- ================================================-- Template generated from Template Explorer using:-- Create Procedure (New Menu).SQL---- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below.---- This block of comments will not be included in-- the definition of the procedure.-- ================================================SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:  <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>-- Add the parameters for the stored procedure here<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>ENDGO

We can write our own stored procedures on the basis of this existing template.

Of course, the content to be modified in the template can be modified in a more convenient way. For example, press Ctrl + Shift + M.

 

In SQL Server Management studio, click the following menu item query --> specify values for templete parameters. The following template parameter value specifies the form:

In this form, you can enter the content you want to modify.

Then we can easily generate the stored procedure we need.

 

What if we bind this template with some of our experiences, that is, we maintain some of our own templates? The method is as follows:

How to configure a template?

In SQL Server Management studio, click the following menu item View --> templete explorer.

In this way, the templete explorer appears on the right side of SQL Server Management studio, as shown in.

Here we can easily maintain our own templates.

 

As shown in the preceding example, some parameters in the template can be specified. How do I use these parameters?

Template parameter requirements

In the preceding template, we see a lot of text similar to <Procedure_Name, sysname, ProcedureName>. This is the template parameter.

The parameter requires three elements: the name of the parameter to be replaced, the Data Type of the parameter, and the default value of the parameter.

Parameters are included in angle brackets (<>) in the following format: <parameter_name, data_type, default_value>.

Parameter_name lists parameters in a stored procedure or function. This field is read-only.

The data type of the parameter in the data_type template. This field is read-only. To change the data type, change the parameters in the template.

Default_value specifies the value of the selected parameter. Default value.

 

 

References:

Create a custom template on SQL Server 2005
Http://www.chinaeda.cn/show.aspx? Id = 13978 & cid = 130
Use the SQL Server Management Studio Template
Http://technet.microsoft.com/zh-cn/library/ms174169.aspx

Create a custom template
Http://technet.microsoft.com/zh-cn/library/ms166841.aspx

About templates in SQL SERVER