ALTERing a Stored Procedure in SSMS 2016

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  March 21, 2017 at 2:25 pm

  #323020

  I know this is probably a dumb question, but why did Microsoft change the output of "Script Stored Procedure as" --> "ALTER To" --> "New Query Editor Window" ?

  It seems that in SSMS 2016, they started using code like this:

  USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mystoredprocedure]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
ALTER PROCEDURE [dbo].[mystoredprocedure]
AS
BEGIN
END
'
END
GO


  whereas in earlier versions of SSMS they don't use sp_executesql. And because of this, I don't see any syntax highlighting or anything. Are they using sp_executesql and dynamic sql for Forced Caching?

  Maybe I'm missing something....can someone clear things up for me?

  Thanks,

  Mike

  Mike Scalise, PMP
  https://www.michaelscalise.com

• Lowell

  SSC Guru

  Points: 323504

  More actions

  March 21, 2017 at 2:39 pm

  #1934222

  that's actually a setting in SSMS that you can toggle back and forth.
  Assuming SSMS 2016, If you go to Tools>>Options>>SQL Server ObjectExplorer>>Scripting
  You currently have a checkmark for "Check for object existence" (True)
  Toggle that, and it will not produce the IF EXISTS, and will produce just the CREATE  TABLE.
  in previous versions it was phrased slightly differently:
  

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Sreekanth B

  SSCertifiable

  Points: 6145

  More actions

  March 21, 2017 at 2:40 pm

  #1934225

  That occurs when "CHECK for Object existence" is turned ON in your SSMS 2016.
  Make sure that option is set to "FALSE" under tools->Options->SQL Server Object Explorer ->Scripting->Object Scripting options.
  That should avoid using sp_executesql

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  March 21, 2017 at 2:45 pm

  #1934227

  Ah, so much better! I must have toggled that setting after installing SSMS 2016 a while ago and then forgot about it. Thanks for the help, guys!

  Mike

  Mike Scalise, PMP
  https://www.michaelscalise.com