[SA 7.1.246 Pro] - FR: Template parameters

[SA 7.1.246 Pro] - FR: Template parameters

We can use $PROMPT()$ macro for creating semi-interactive snippets, which is nice, I use it all the time. But we have some install/update/maintenance scripts that are saved in files and it wouldn't suffice to move them into snippets. Making changes to the scripts would require constant updates to the snippets, meaning there would be lots of compares requiring exports, etc. Tedious and error prone.

If there were support for Template Parameters (the kind of templates SSMS generates when creating a new stored procedure/function/whatever from Object Explore) built in into SA that would be great.

EDIT: for compatibility reasons, the <name> type templates should be recognized but using <and> for delimiter (like in SSMS) might not be the best choice. The chance that completely valid code will contain something that will be interpreted as a template, but is not, is pretty high.

Thank you for your suggestion. Please help me understand better what you are describing and what would be the connection between template-like files and snippets

Here is an SSMS template for new table function

	Code:
	--================================================ -- Create Inline Table-valued Function template --================================================ USE <database_name> GO IF OBJECT_ID (N'<schema_name>.<function_name>') IS NOT NULL DROP FUNCTION <schema_name>.<function_name> GO CREATE FUNCTION <schema_name>.<function_name>(<parameter1_name> <parameter1_type>) RETURNS TABLE AS RETURN ( <T> ) GO

Can you please convert it to an example demonstrating how $PROMPT()$ and perhaps other macros could be used here?

They're not closely related. Check this template created by SSMS for a new stored procedure.

Code:

-- ================================================
-- 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author>
-- Create date: <Create>
-- Description: <Description>
-- =============================================
CREATE PROCEDURE <Procedure_Name>
-- Add the parameters for the stored procedure here
<Param1> <Datatype_For_Param1> = <Default_Value_For_Param1>,
<Param2> <Datatype_For_Param2> = <Default_Value_For_Param2>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <Param1>, <Param2>
END
GO

As you can see, it defines templates by using

	Code:
	<name>

Hmm. The code here is ruined :) I should see name, type, default value between angle brackets but I only see name. I guess the same happened to your code as well.

Nevertheless, the whole shebang is pretty loose, it does not enforce presence of any parts (except maybe for the name), you can leave both type (which is kind of description anyway) and the default value as well. And it fails miserably when it comes to a code part that looks like this :)

	Code:
	SELECT t.column1 FROM dbo.table1 AS t WHERE t.column2 <4> 9

EDIT: imagine that you see t.column2 less_than 4 AND t.column3 = COALESCE(t.column4 ,t.column5 ,t.column6) AND t.column7 greater_than 9 in the WHERE part :)

Where this could perform better than $PROMPT()$ macro is when you have some highly customized, frequently edited report/install script/whatsoever that are stored in files maintained by multiple developers. I tried to create snippets for them so that I could recreate the scripts very fast by using $PROMPT$ to fill in the customized parts but the constant contribution to the files themselves made me give it up pretty quickly. I had to retrieve the code from the snippet, compare it to the one in the repository, apply changes, save as snippet and that become a tedious work to do on daily basis just for the sake of not being forced to search-replace tags. Not to mention that some files were rather large and earlier versions of SA had some difficulties editing those snippets.

It couldn't replace $PROMPT$ since $PROMPT$ can choose from options, and is capable of producing completely different outputs based an answers. Another advantage of $PROMPT$ is that it's linear, you don't have to think, you just have to answer the question or chose from options. And this its weakness at the same time. Sometimes the answer to be given can depend on previous answers. Using $PROMPT$ those aren't visible anymore, you have to keep them in mind. The way SSMS handles templates you can review all the settings simultaneously and press OK when you're sure everything is set.