Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is SSMS altering my stored procedures (re-formatting, changing exec to EXECUTE, etc.)

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.

like image 206
M74d3 Avatar asked Jan 02 '18 19:01

M74d3


People also ask

What is wrong with stored procedures?

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?).

How do I fix a stored procedure in SQL?

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.

Does altering a stored procedure recompile it?

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.


1 Answers

This happens when you are working with Always Encrypted and have the option "Enable Parameterization for Alway Encrypted" enabled.

To fix:

  1. Go to: Query > Query Options > Execution > Advanced
  2. un-check 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.

like image 137
Ashley Medway Avatar answered Nov 06 '22 01:11

Ashley Medway