How can we help you today? How can we help you today?

What alternative to exec('sql')

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?
SkipSailors
0

Comments

2 comments

Sort by
  • 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!)

    Jessica R
    0
  • 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

    ben_b
    0

Add comment

Please sign in to leave a comment.