# Dynamic “compilation” of SQL stored procedures

I have a stored procedure which is called to create ( "compile" ) temporary stored procedures, code below, based on whether a flag in a database record is set - the various sub-procedures such as CompileListHandler execute the matching CREATE PROCEDURE statement using execute( @sql ).

( To explain the context, the created SQL procedure handles a http web request, the specification is from the database, and is occasionally updated, at which point "compiled" is set false, so that the associated stored procedure in the temp schema is created (or re-created) before it is executed ).

In the past I have occasionally seen errors, due to two clients executing the procedure in parallel for the same record. I have just put in the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement, but I am unsure if (a) this will prevent the errors and (b) possibly cause any other issues ( perhaps deadlocks, although I don't see why there would be a deadlock, but that may be a lack of imagination ). It appears to be working ok, but on the other hand the errors were quite rare anyway.

Please review for concurrency correctness ( and anything else ).

procedure [perfect].[CompileHandler] @h int as

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

declare @kind int, @name nvarchar(max), @compiled int
select @kind = Kind, @name = Name, @compiled = compiled from perfect.Handler where id = @h

if @compiled = 1 return

if exists ( select 1 from information_schema.routines where routine_schema = 'temp' and routine_name = @name )
begin
declare @sql nvarchar(max) select @sql = 'drop procedure temp.' + QUOTENAME(@name)
execute(@sql)
end

if @kind=1 exec perfect.CompileListHandler @h
else if @kind=2 exec perfect.CompileShowHandler @h
else if @kind=3 exec perfect.CompileAddHandler @h
else if @kind=4 exec perfect.CompileEditHandler @h
else if @kind=5 exec perfect.CompileFindHandler @h

if exists ( select 1 from information_schema.routines where routine_schema = 'temp' and routine_name = @name )
update perfect.Handler set Compiled = 1 where id = @h