Inline Stored Procedure with OUTPUT parameters

a.higginsa.higgins Posts: 90 Bronze 2
edited August 22, 2016 6:48AM in SQL Prompt
The new "Inline stored procedure" command is quite nice, but it does not seem to be able to handle OUTPUT parameters.

When I use it on a procedure with one normal and three OUTPUT parameters, the normal parameter is declared and populated, but the output parameters are never declared.

The resulting script then throws an error: must declare the scalar variable "@Whatever".

Comments

  • Hi a.higgins,

    Thanks for giving the beta a test!

    Unfortunately we can't reproduce this here, would you be able to send us a reproducible script for both the CREATE and EXEC so we can test against? If it contains sensitive information you can email us at sqlpromptteam@red-gate.com.

    Best regards,

    Davdi
  • a.higginsa.higgins Posts: 90 Bronze 2
    Here's a genericized version of my stored procedure:
    CREATE PROCEDURE spSampleOutput
    (
    	@Date DATETIME2(3)
       ,@Int INT = 0 OUTPUT
    )
    AS
    	SELECT @Int = DATEDIFF(DAY, @Date, GETDATE())
    

    If I create that stored procedure, open up a new query, and type "Execute spSampleOutput", SQL Prompt will auto-fill the details for me:
    EXECUTE dbo.spSampleOutput
    	@Date = '2016-08-18 14:42:29'
    	, -- datetime2(23)
    	@Int = 0 -- int
    

    Right-clicking spSampleOutput and choosing "Inline stored procedure" generates the following code:
    DECLARE @Date DATETIME2(3);
    SET @Date = '2016-08-18 14:42:29';
    SELECT 0 = DATEDIFF(DAY, @Date, GETDATE()) -- int
    

    Attempting to execute it, naturally, results in the following error:
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '='.
    
  • Hi a.higgins,

    Thanks for sending this through! We can reproduce it here and will look into it.

    Best regards,

    David
  • Harry FrankishHarry Frankish Posts: 53 New member
    Hi a.higgins

    It looks like there are 2 issues you're hitting:
      1) Autocomplete for execute statements doesn't deal with output parameters properly 2) Inlining an execute statement produces invalid SQL whzen the output parameters aren't specified properly

    We've fixed 2) in the latest beta build (7.3.0.437) and will be looking into 1) over the next few days. We've seen your UserVoice request for this here

    Thanks again for your feedback.

    Harry
Sign In or Register to comment.