Skip to content
← SQL Prompt

How can we improve SQL Prompt?

SQL Prompt

Categories

JUMP TO ANOTHER FORUM

(thinking…)

Add OUTPUT parameters when executing a stored procedure

When inserting code to execute a stored procedure, SQL Prompt currently automatically generates input variables of the correct data type, e.g.:

EXECUTE ETL.spLoad_DimPerson
@LatestLoadDate = '2016-06-21 18:51:43', -- datetime2
@TargetRowsInserted = 0, -- int
@TargetRowsUpdated = 0, -- int
@TargetRowsDeleted = 0 -- int

However, the syntax does not seem to recognize OUTPUT parameters. When I ask SSMS to generate a query for me by right-clicking and choosing 'Execute stored procedure...', it gives me the following:

DECLARE @return_value int,
@TargetRowsInserted int,
@TargetRowsUpdated int,
@TargetRowsDeleted int

EXEC @returnvalue = [ETL].[spLoadDimPerson]
@LatestLoadDate = '2005-06-21 18:41:59',
@TargetRowsInserted = @TargetRowsInserted OUTPUT,
@TargetRowsUpdated = @TargetRowsUpdated OUTPUT,
@TargetRowsDeleted = @TargetRowsDeleted OUTPUT

SELECT @TargetRowsInserted as N'@TargetRowsInserted',
@TargetRowsUpdated as N'@TargetRowsUpdated',
@TargetRowsDeleted as N'@TargetRowsDeleted'

SELECT 'Return Value' = @return_value

It would be very helpful if SQL Prompt would automatically provide the code to see the result of OUTPUT parameters when completing an EXECUTE statement

15 votes

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

Andrew Higgins shared this idea  ·   ·  Report…  ·  Admin →
completed  ·  Thomas Walsh responded  · 

Thank you for your suggestion.

We’ve reviewed this as part of our UserVoice triage.

Currently this feature is available in SQL Prompt.
The OUTPUT and EXECUTE statements are auto generated.

We don’t support the SELECT statement as part of this feature.

Please feel free to raise a new feature request if this is something you would like.

Kind Regards,
Prompt Team

Show previous admin responses (3)

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • ThoschVX commented  ·   ·  Report

    This would be a great feature... :-)

  • Robert commented  ·   ·  Report

    Looks like it's only implemented partially. The SELECT is missing for output parameters. At least with the current version it's missing.

  • Doug Heeren commented  ·   ·  Report

    It looks like this is a part of the version I'm on, but it isn't scripting Decimals correctly and is losing the precision.

  • Andrew Higgins commented  ·   ·  Report

    To clarify, I'd like to see it generate the following code (wish this place had an edit button):

    DECLARE
    @TargetRowsInserted INT
    ,@TargetRowsUpdated INT
    ,@TargetRowsDeleted INT

    EXECUTE ETL.spLoad_DimPerson
    @LatestLoadDate = '2016-06-21 18:51:43', -- datetime2
    @TargetRowsInserted = @TargetRowsInserted OUTPUT, -- int
    @TargetRowsUpdated = @TargetRowsUpdated OUTPUT, -- int
    @TargetRowsDeleted = @TargetRowsDeleted OUTPUT -- int

    SELECT
    @TargetRowsInserted 'TargetRowsInserted'
    ,@TargetRowsUpdated 'TargetRowsUpdated'
    ,@TargetRowsDeleted 'TargetRowsDeleted'