[SA 7.3.435 Pro] - Refactoring Rules for SQL Server Ref

[SA 7.3.435 Pro] - Refactoring Rules for SQL Server Ref

There's a minor inconsistency in Refactoring Rules for SQL Server Refactoring behavior. In spite of not being able to select the $PROMPT()$ macro, simply pasting it into the code it still executes. Though the result is not inserted. It would be nice if that macro would work there as well. Are there any reasons not allowing it?

EDIT: Also, could you make it possible to assign shortcuts to refactoring (extract view/procedure)?

I'm not sure that is doable. Refactoring is different from snippets processing, it supports only a subset of the macros, $PROMPT()$ isn't one of them, as well it supports several refactoring-only macros, like references to old and new names, which are not supported in the snippets.

Could you check that with the team, please? Refactoring supports $$...$$ macros which, I guess, are way more difficult to implement than $PROMPT()$. It even executes $PROMPT()$ macros that are copy-pasted from a snippet code into the refactoring rule, I get the dialogs and their behavior is the same as it is in case of snippets. The only thing that doesn't happen is the replacement of the $PROMPT()$ macro variable names with the selected/entered value.

What about the shortcuts/hotkeys for refactoring? I imagine there would be some difficulties for different servers but could they be done?

I'm afraid $PROMPT()$ is unintentional there, it's not listed in the supported macros in the refactoring templates. Please open the Options dialog, select any refactoring template and then click the Macro drop-down menu (button) in the top-right corner. $PROMPT()$ isn't there.

But to be certain, I will check the dev. team.

I can also ask if it could be supported, I'm sure there is a reason for why not all macros supported by snippets are available in refactoring templates.
Could you please do me a favor and describe a use case for $PROMPT()$ in refactoring?

Sure. I was somewhat "unaware" of this refactoring (namely, extracting codes to procedure) and I made a snippet that would process the selected code part and turn it into a stored procedure. It isn't nearly as sophisticated as its refactoring counterpart, it doesn't actually extract the code but simply surrounds the selected text with a CREATE PROCEDURE structure. What it does in addition, is that it throws a series of prompts that ask whether there should be a part granting rights (GRANT EXECUTE ON OBJECT...) to roles, a part adding description to the objectsp_addextendedproperty (sp_addextendedproperty and @value), should it include RECOMPILE, should it encapsulate the innards in TRANSACTION and a couple of other things that would build a large piece of code around the selected text based around a few simple questions. I wouldn't dare to execute it right away from the snippet (in case there was syntax error, it would fail, and I'd have to start it all over again), so I usually copied the selected code to another tab, run the snippet there, made some fine tuning there and then replaced the selection in the original tab with the execute part. It also wasn't capable to extract the variables into stored procedure parameters like the refactoring does.

That's why I'd shift to refactoring instead if I was able to do the same things as in the snippet.

Could you please provide an example of how you used %$PROMPT(..)$ in a refactoring template? Developers say it should work in your version, even though it's not documented

Here is their example

	Code:
	$PROMPT(v,Enter object name,test,Object name)$ $v$

Sure. Here is it:

Code:

$prompt(@schema_name,Enter schema name,dbo,Create procedure)$
$prompt(@proc_name,Enter procedure name (will be prefixed with 'usp_'),,Create procedure)$
$PROMPT(@recompile,Recompile?,,Create procedure,"-- " "","No\, thanks!" "Yes\, why not?")$
$PROMPT(@description,Want to add description?,1,Create procedure,"-- " "","No\, thanks!" "Yes\, why not?")$
$PROMPT(@debug_mode,Debug mode?,2,Create procedure,1 0 NULL,"Default ON" "Default OFF" Devset)$
CREATE PROCEDURE $@schema_name$.usp_$@proc_name$
/***********************************************************
* Procedure description: This procedure is used for: <<purp_desc>>
*
* Date: $DATE$
* Author: $OSUSER$
*
* Changes
* Date Modified By Comments
************************************************************
* $DATE$ $OSUSER$ - Initial dev version
* newve
************************************************************/

/************** example ********************/

--************* example ********************/
(
|
@debug INT = $@debug_mode$
) $@recompile$WITH RECOMPILE
AS
BEGIN

IF OBJECT_ID('tempdb..##dev_settings', N'U') IS NOT NULL AND @debug IS NULL
SELECT @debug = ds.setting
FROM ##dev_settings AS ds
WHERE 1 = 1 AND ds.[name] = 'debug' AND ds.setting = OBJECT_ID('$@schema_name$.usp_$@proc_name$');

IF @debug != 0 SET NOCOUNT OFF ELSE SET NOCOUNT ON

DECLARE @log AS TABLE (id_log INT NOT NULL IDENTITY(1,1) PRIMARY KEY, log_entry NVARCHAR(MAX), log_entry_int BIGINT, entry_date DATETIME2(7) DEFAULT sysdatetime());

/* crap starts here */

$REFACTORING_CODE$

/* crap ends here */

IF @debug != 0 SELECT * FROM @log AS l;
RETURN @@ERROR
END

I also played with $REFACTORING_OBJ_NAME$ so that I could spare using prompts for schema and procedure but it turned out that though the code part $REFACTORING_OBJ_NAME$ is regenerated after every keystroke in Procedure Name editbox, the one that's enclosed in $$...$$ does not, hence something like

	Code:
	$$SELECT ISNULL(PARSENAME('$REFACTORING_OBJ_NAME$', 2) + '.', '') + PARSENAME('$REFACTORING_OBJ_NAME$', 1)$$

would not work as a replacement and I found no other way enforcing 'usp_' to the beginning of the name.

The funny thing is that now that I tried again it worked. The first time it refused to replace prompt variables in code with their values. Couldn't find out why so I gave up after a while. I should have been a bit more persistent.
I'm glad it works now but I still think this feature deserves a decent hotkey on its own.