SSMS is suddenly re-formatting my stored procedure - it has never done this before. Here's an example of what I'm talking about. Here's the start of the stored procedure I created last week:
CREATE PROCEDURE [dbo].[usp_LoanDataNames]
(
@LoanID varchar(max) = null,
@Columns varchar(max) = null,
@DataNames NVARCHAR(MAX) = '',
@SQL NVARCHAR(MAX) = ''
)
AS
All the stored procedures I created before today still have that format. But when I create a new stored procedure today, with the same exact format as above, and then pull up the stored procedure script, it looks like this:
CREATE PROCEDURE [dbo].[usp_LoanDataNames2]
@LoanID VARCHAR (MAX)=NULL, @Columns VARCHAR (MAX)=NULL, @DataNames NVARCHAR (MAX)='', @SQL NVARCHAR (MAX)=''
AS
This is a different format than before. It moved all the parameters up to immediately follow the stored procedure name, and put them all on the same line. Also (and I didn't bother posting the entire stored procedure so I didn't post this part), it changes Exec
to Execute
. And it changes the indenting on various lines, and changes many lowercase words to uppercase.
It even will change the formatting if I do it this way:
CREATE PROCEDURE [dbo].[usp_LoanDataNames]
AS
DECLARE
@LoanID varchar(max) = null,
@Columns varchar(max) = null,
@DataNames NVARCHAR(MAX) = '',
@SQL NVARCHAR(MAX) = ''
)
It will change that to:
CREATE PROCEDURE [dbo].[usp_LoanDataNames2]
@LoanID VARCHAR (MAX)=NULL, @Columns VARCHAR (MAX)=NULL, @DataNames NVARCHAR (MAX)='', @SQL NVARCHAR (MAX)=''
AS
Any idea why this is happening? We have certain standards we use here, and the original stored procedure is formatting my company prefers. The problem is I CAN'T use that formatting anymore, because every single stored procedure I create or alter automatically re-formats now. Any ideas?
Thank you.
Stored procedures introduce a cliff (or disconnect) between coherent functionality, because the domain logic gets split between the application- and the database layer. It's rarely clear where the line is drawn (e.g. which part of a query should go into the application layer and which part into the database layer?).
Use SQL Server Management StudioExpand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure to modify, and then select Modify. Modify the text of the stored procedure. To test the syntax, on the Query menu, select Parse.
Use the ALTER PROCEDURE statement to explicitly recompile a standalone stored procedure. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.
This happens when you are working with Always Encrypted and have the option "Enable Parameterization for Alway Encrypted" enabled.
To fix:
Query > Query Options > Execution > Advanced
Enable Parameterization for Alway Encrypted
(its at the end of the list)You should only have this option enabled when you are working with ad-hoc queries and not stored procedures.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With