What alternative to exec('sql')

SkipSailors

I have definitions of stored procedures in sql scripts that I store in m SCC.  (I started this before RedGate offered such services, and my process keeps working for me.)  I keep the script in an ALTER PROCEDURE statement.  When I change something in a procedure, I can run the modified ALTER PROCEDURE to publish the changes.  I don't DROP and CREATE, so I don't risk losing GRANTS and other information that might have been applied outside my purview.  This works except when the procedure down't exists.

So, my pattern is that I have an if statement that checks the sys.objects for the procedure, and it it isn't there, creates a minimal one that the ALTER can use.

<div>&nbsp; &nbsp; if not exists (select * from sys.objects where object_id = OBJECT_ID(N'whatever') and type in (N'P', N'PC'))

</div><div>	&nbsp; &nbsp; exec ('CREATE PROC whatever AS SELECT 1')</div><div>
&nbsp; &nbsp; go</div>
&nbsp; &nbsp; alter procedure whatever ...<br>

SQL Prompt explains to me (BP013) that I should avoid exec('...').  What other ways do I have to create a generic minimal procedure if there is not one present?

Jessica R

Hi @SkipSailors!

The recommendation is to use  sp_executesql instead of exec https://documentation.red-gate.com/codeanalysis/best-practice-rules/bp013

However can I just confirm if that wouldn't work in this case? (Apologies as I'm not a SQL developer myself!)

ben_b

hi @SkipSailors - if you have SQL Server 2016 SP1 or onwards you can use the CREATE OR ALTER PROCEDURE statement.

example below:

CREATE OR ALTER PROCEDURE dbo.sp_test

AS

SELECT 1;

GO

EXEC dbo.sp_test;

go

CREATE OR ALTER PROCEDURE dbo.sp_test

AS

SELECT 2

go

EXEC dbo.sp_test