msimone - Thursday, February 1, 2018 4:42 AM

Hello, i have a discussion with a co-worker about normal code or dynamic code in a OLTP.
The example is:

create table dbo.test (id smallint primary key, description nvarchar(50))

go

create procedure dbo.test1 (@Id smallint = null)
as
declare @comando nvarchar(1024)
set @comando='select id,description from dbo.test '
set @inter='@id smllint'
if @id is not null
 set @comando=@comando+'where id=@id'

exec sp_executesql @comando,@inter,@id=@id

go

create procedure dbo.test2 (@id smallint,@rows int)
as
select top (@rows) id,description from dbo.test where @id>=@id

In yours opinión, what is the best solution for a lot of executions?