Options
What alternative to exec('sql')
SkipSailors
Posts: 5 Bronze 2
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.
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?
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> if not exists (select * from sys.objects where object_id = OBJECT_ID(N'whatever') and type in (N'P', N'PC'))
</div><div> exec ('CREATE PROC whatever AS SELECT 1')</div><div>
go</div>
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?
Tagged:
Answers
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 Ramos | Product Support Engineer | Redgate Software
Have you visited our Help Center?
example below: