MS SQL stored procedure wrong parameter list

Answered

Hi there.

I have latest DataGrip 2020.1 for MacOs (the same issue was with 2019.3.3, 2019.3.4), MS SQL Server 2008 R2, driver version is 8.2.2 (the same issue was with 7th driver version).

The problem is that DataGrip shows wrong parameter list sequence when calling stored procedure. I.e. when parameter list is: param1, param2, param3 it shows "param1, param3, param2".

Example:

ALTER PROCEDURE dbo.addVehicleRegister
@vehicle_guid NVARCHAR(36),
@device_id NVARCHAR(18),
@ibutton NVARCHAR(18),
@state_number NVARCHAR(10),
@dt_period NVARCHAR(20),
@is_active BIT

In database tree it shows (bit parameter must be the last):

If I try to get the source of SP from this tree it shows correct code - the same as I have in my source file.

And in query:

When I right-click on this query the "Hints settings..." do nothing! It didn't open any settings. In the "Preferences"->"Hints" there is nothing interesting to set.

This happens with different projects, different databases (but they are the same 2008R2).

Is it settings or DataGrip issue? How to fix it?

3 comments
Comment actions Permalink

Pavel Varnavsky,

I filed an issue based on your description https://youtrack.jetbrains.com/issue/DBE-10485.

This situation happens when you create a procedure and then alter it ?

0
Comment actions Permalink

vasily chernov,

Took another investigation...

My actual code of creating and altering SP:

IF object_id('dbo.addVehicleRegister', 'p') IS NULL
BEGIN
exec ('CREATE PROCEDURE dbo.addVehicleRegister AS SELECT 1')
END
GO

ALTER PROCEDURE dbo.addVehicleRegister
@vehicle_guid NVARCHAR(36),
@device_id NVARCHAR(18),
@ibutton NVARCHAR(18),
@state_number NVARCHAR(10),
@dt_period NVARCHAR(20),
@is_active BIT
AS
BEGIN
...

So I've created copy of this SP:

IF object_id('dbo.addVehicleRegister1', 'p') IS NULL
BEGIN
exec ('CREATE PROCEDURE dbo.addVehicleRegister1 AS SELECT 1')
END
GO

ALTER PROCEDURE dbo.addVehicleRegister1
@vehicle_guid NVARCHAR(36),
@device_id NVARCHAR(18),
@ibutton NVARCHAR(18),
@state_number NVARCHAR(10),
@dt_period NVARCHAR(20),
@is_active BIT
AS
BEGIN
...

And when I run it first time, it was created correctly:

I changed parameter order:

IF object_id('dbo.addVehicleRegister1', 'p') IS NULL
BEGIN
exec ('CREATE PROCEDURE dbo.addVehicleRegister1 AS SELECT 1')
END
GO

ALTER PROCEDURE dbo.addVehicleRegister1
@vehicle_guid NVARCHAR(36),
@device_id NVARCHAR(18),
@ibutton NVARCHAR(18),
@state_number NVARCHAR(10),
@is_active BIT,
@dt_period NVARCHAR(20)
AS
BEGIN
...

Run it and: SP source changed correctly, but parameters list in database tree and hints are remains the same - bit parameter on last place.

Edit:

Made step 3: added one more parameter to SP:

IF object_id('dbo.addVehicleRegister1', 'p') IS NULL
BEGIN
exec ('CREATE PROCEDURE dbo.addVehicleRegister1 AS SELECT 1')
END
GO

ALTER PROCEDURE dbo.addVehicleRegister1
@vehicle_guid NVARCHAR(36),
@device_id NVARCHAR(18),
@ibutton NVARCHAR(18),
@state_number NVARCHAR(10),
@is_active BIT,
@dt_period NVARCHAR(20),
@test_param BIGINT
AS
BEGIN
...

Run it and it was just added to the end of parameters list:

0
Comment actions Permalink

Cool, thank you for further investigation.

It's better to continue discussion in the corresponding issue.

0

Please sign in to leave a comment.