SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 7.3.435 Pro] - Refactoring Rules for SQL Server Ref

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 7.3.435 Pro] - Refactoring Rules for SQL Server Ref
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 7.3.435 Pro] - Refactoring Rules for SQL Server Ref Reply with quote
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)?
Sat Dec 05, 2015 12:59 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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.
Sun Dec 06, 2015 6:53 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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?
Sun Dec 06, 2015 8:03 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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?
Mon Dec 07, 2015 2:12 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.
Mon Dec 07, 2015 4:10 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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$

Fri Dec 11, 2015 10:16 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.
Fri Dec 11, 2015 12:00 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.