[SA 9.0.0152 Pro BETA] - FRs: autocomplete upon code insert

[SA 9.0.0152 Pro BETA] - FRs: autocomplete upon code insert

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.

	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.

Re: [SA 9.0.0152 Pro BETA] - FRs: autocomplete upon code ins

	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 :(

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

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.

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.

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.