SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.0152 Pro BETA] - FRs: autocomplete upon code insert

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.0152 Pro BETA] - FRs: autocomplete upon code insert
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 9.0.0152 Pro BETA] - FRs: autocomplete upon code insert Reply with quote
When selecting a stored procedure from popup after typing EXECUTE, the inserted code is formatted like:
Code:

EXECUTE energetikus.usp_get_nyomvonali_szorzok
    @id_nyomvonal = null,
    @mikor = null,
    @kuszobertek = null,
    @debug = null

even though code formatting parameters for commas are set to Stacked (leading). Reformatting that code part will end up as:
Code:

EXECUTE energetikus.usp_get_nyomvonali_szorzok
        @id_nyomvonal = NULL
       ,@mikor = NULL
       ,@kuszobertek = NULL
       ,@debug = NULL

though I'm not sure if that's the result of settings for commas or that there are formatting rules for EXECUTE and EXEC (I guess it's the latter). It would be nice if the code would be inserted formatted using the default formatting rules. The same applies for INSERT INTO ... VALUES.
Code:

INSERT INTO dims.adatgyujtok
(
    id_adatgyujto,
    id_jarmu,
    datum_tol,
    datum_ig,
    id_adatgyujto_tipus,
    adatgyujto_ertek,
    id_verzio,
    megbizhato_e
)
VALUES
(
    /*{ id_adatgyujto }*/,
    /*{ id_jarmu }*/,
    /*{ datum_tol }*/,
    /*{ datum_ig }*/,
    /*{ id_adatgyujto_tipus }*/,
    /*{ adatgyujto_ertek }*/,
    /*{ id_verzio }*/,
    /*{ megbizhato_e }*/
)


It would also be a bit more useful if it would insert the argument types (or default value if there's one) for stored procedures in comments like you use for VALUES (the ones that get selected once the cursor is in them) instead of inserting null there.

And to add another one (without any consideration about it being feasible or not), it would be wonderful if we were able to cycle through the argument values (or at least step to next one) using eg. TAB. This one could prove pretty nasty to implement, I admit, as it would require entering the editor into a state where it is possible adding and maintaining markers and jumping between them. Since SQL Editor already handles some kind of markers, I imagine this one not to be impossible after all.

Probably wouldn't work in other editors (eg. SSMS), though it has something similar that is used for creating a stored procedure, where it inserts this code into editor:
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

except that upon pressing CTRL+SHIFT+M it pops up a dialog window where you can edit everything that is surrounded by markers (<and> here). The drawback is that SA cannot be used in that dialog.
Thu Sep 29, 2016 5:02 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
Quote:
When selecting a stored procedure from popup after typing EXECUTE, the inserted code is formatted like ...



Code inserted from an Intellisense popup is not formatted immediately using formatting rules in the Options. that's why you see the difference there. It requires an extra selection and a key press. It's one of the things we have in our to-do list for future versions.


Quote:
And to add another one (without any consideration about it being feasible or not), it would be wonderful if we were able to cycle through the argument values (or at least step to next one)

Thank you, this is a very good suggestion. We will consider implementing it in a future maintenance release.
Thu Sep 29, 2016 9:54 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Re: [SA 9.0.0152 Pro BETA] - FRs: autocomplete upon code ins Reply with quote
gemisigo wrote:

It would also be a bit more useful if it would insert the argument types (or default value if there's one) for stored procedures in comments like you use for VALUES (the ones that get selected once the cursor is in them) instead of inserting null there.


Hmm. I just learned that even though there are columns in the system tables meant just for that simple job, namely storing the default values for parameters, eventually it is not done. Not even the fact there's a default value. Why?!? That's plain dumb :(
Thu Sep 29, 2016 10:42 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
Just in case
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/900756fd-3980-48e3-ae59-a15d7fc15b4c/how-can-i-find-the-default-values-for-a-stored-procedure-parameter?forum=transactsql
Thu Sep 29, 2016 11:17 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Yes, I've seen that. But I consider that to be too much of a hassle. I already created a snippet that inserts types in replaceable comments instead of NULL. That was pretty easy. I'm too mad to go any further. Well, at least for now :)

What I don't understand is why MS does not fill those columns if they're already there.
Thu Sep 29, 2016 1:29 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
I believe the mentioned columns are for CLR functions that cannot be parsed in run time. The system view in question provides an aggregate view to all functions. Here is fun part, default parameters in CLR are strongly typed and stored in binary format. In t-SQL they are not. Moreover, I think the value may be different on different systems depending on where you run your code. For example

CREATE PROCEDURE test(@date DATE = '05/01/2016') AS SELECT month(@date) AS D;

Here, is that a January 5th or May 1st? It depends on the locale. Copy same code to a different server or change the session language, and you may potentially get different result.
Try with
SET LANGUAGE 'English'
EXEC test

And then
SET LANGUAGE 'German'
EXEC test

Same procedure, different results.

I'm not saying that the default parameters for t-SQL procedures couldn't be exposed via sys.all_parameters view, but there are obviously some challenges.
Thu Sep 29, 2016 1:57 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
This is one more reason they should store the value at creation time, so that it retains its value as it was intended, instead of depending on the current setting.

I cannot test your example but I accept if you say it would return 5 in one case and 1 in the other. Now, if that date would be stored upon creation, and the stored procedure would be called using the default, it would yield the same result regardless of the locale. You might argue that providing the 'same' parameter would produce different results anyway with the different locale, but then I say that those aren't the 'same' values. They might 'look' the same but they aren't since they are interpreted differently. On the other hand, if you provided the procedure with the same values, which would 'look' different, the procedure would return the same results. And this is the reason for storing that value instead of reinterpreting it on the fly, because you call with default by using DEFAULT keyword or omitting the named parameter completely. Not to mention that using unambiguous formats like 'yyyymmdd' would just add another volume to the chaos for not willing to play unfair.

These issues with dates are known for ages (pun intended), it's really strange why all these crappy ambiguous date formats haven't been banned. And purged with fire, just to make it sure they don't come back.

Never mind, if I had some time on hand I'd implement that as a snippet. Alas, I cannot afford it...

EDIT: also, my last request would be much more useful than this one, so I really hope you get to make that one come true.
Thu Sep 29, 2016 2:21 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.