Creating Stored Procedure template in VS2010?

how can we create a stored procedure template and then import it to vs2010, so that whenever I create a new stored procedure that template is loaded and I already have some set 开发者_运维技巧of commands in my stored proc.

I have googled it, but haven't found much on it. can anyone tell me the procedure or provide me the link where I can learn creating stored proc templates for vs2010.

thanks in advance

First you need to create the template file, here is a simple example of what I needed

MySqlProc.sql

DELIMITER $
DROP PROCEDURE IF EXISTS `$fileinputname$`;
$
CREATE PROCEDURE `$fileinputname$`
(

)
BEGIN   
END
$

As you guessed, the $fileinputname$ keyword will be replaced with the file name the user inputs.

Then you need to create an XML file with .vstemplate extension. Mine looked like this:

MySqlProc.vstemplate

<VSTemplate Type="Item" Version="2.0.0"
    xmlns="http://schemas.microsoft.com/developer/vstemplate/2005">
  <TemplateData>
    <Name>MySql Stored Procedure</Name>
    <Description>Creates SQL file with MySql stored procedure prerequisites </Description>
    <Icon>mysql.ico</Icon>
    <ProjectType>CSharp</ProjectType>
    <DefaultName>MySqlStoredProcedure.sql</DefaultName>
  </TemplateData>
  <TemplateContent>
    <ProjectItem ReplaceParameters="true" >MySqlProcedure.sql</ProjectItem>
  </TemplateContent>
</VSTemplate>

I created my own icon, just for fun, otherwise you get an ugly generic icon by default.

Zip the 2 files you created + optionally the icon file. Copy the zip to Documents\Visual Studio 2010\Templates\ItemTemplates

Your new template should appear, no need to restart Visual Studio or anything like that.