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?


Sign In or Register to comment.