Inline Stored Procedure with OUTPUT parameters

a.higgins

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".

David Priddle

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.higgins

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 '='.

David Priddle

Hi a.higgins,

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

Best regards,

David

Harry Frankish

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