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.

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

	    exec ('CREATE PROC whatever AS SELECT 1')
    alter procedure whatever ...

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?


Sign In or Register to comment.