Skip to main content
Merged

Make use of CREATE OR ALTER

Related products:Software Factory
  • August 15, 2022
  • 2 replies
  • 95 views

Forum|alt.badge.img+5

The current way of editing a Stored Procedure returns the following method to create the syntax on the database:

if exists (select 1 from sysobjects
where name = 'stored_proc_name' and type = 'P')
drop procedure stored_proc_name
go

create procedure stored_proc_name
as
begin

However, when using Query Store, the relevant data will be untrackable as the object_id is different after creating.

A possible solution would be to make use of the 

CREATE OR ALTER dbo.stored_proc_name
AS
BEGIN

This makes sure the object_id isn't changing, so performance impact of the new version can be tracked more easy and if a specific plan is forced, this won't break.

2 replies

Mark Jongeling
Administrator
Forum|alt.badge.img+23
  • Software Architect
  • August 16, 2022
Updated idea statusNewDuplicate
Idea merged into:

All the votes from this idea have been transferred.

Mark Jongeling
Administrator
Forum|alt.badge.img+23
  • Software Architect
  • August 16, 2022

Hi Peter,

We have it on our backlog 😁