Inline Stored Procedure with OUTPUT parameters

• 
  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

  David Priddle August 18, 2016 10:36

  - 0 +
• 
  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 '='.
  

  a.higgins August 18, 2016 10:46

  - 0 +
• 
  Hi a.higgins,
  
  Thanks for sending this through! We can reproduce it here and will look into it.
  
  Best regards,
  
  David

  David Priddle August 18, 2016 10:52

  - 0 +
• 
  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

  Harry Frankish August 22, 2016 06:48

  - 0 +

Add comment

Please sign in to leave a comment.