sql server - Are stored procedures declarative or imperative? -

please read following example first.

i have database table named product , table has column named id data type int.

i'm writing stored procedure adds new product. 1 of rules of database when adding new product, id assigned must smallest integer started 1, , of course ids unique.

for example, if existent ids 1, 2, 3, 4, 5, 6 new product have id of 7. if existent ids 1, 2, 3, 5, 6, 8 new id 4.

this try:

declare @newid int set @newid = 1 while exists (select * product product.id = @newid)     set @newid = @newid + 1 /*then use @newid insert new item table product*/ 

but friend told me code not efficient because query inside while loop's condition evaluated each iteration.

and part of code:

declare @currentid int, @lastid int, @newid int set @lastid = 0  declare idcursor cursor select product.id product order product.id open idcursor fetch next idcursor @currentid  while @@fetch_status = 0     begin         if @currentid <> @lastid + 1             break          set @lastid = @currentid         fetch next idcursor @currentid     end  set @newid = @lastid + 1 close idcursor deallocate idcursor /*then use @newid insert new item table product*/ 

in opinion, sql declarative language, no matter write our codes dbms rearrange them achieve execution plan, give optimization part dbms , try keep things simple , more readable. besides, finding new id small part of stored procedure.

but in friend's opinion, when writing stored procedure becomes imperative language , code author takes responsibility code efficiency. , said did make things simple me, not system.

so questions are:

1. is sql still or no more declarative when write stored procedures? or it's somewhere in middle?

2. what considered better practice when writing store procedures: keeping them simple or taking care of efficiency?

i argue every language mixture of call declarative , imperative.

a person can write bad code (or great code) in language. in opinion, if want write great code, must know strengths , weaknesses of language writing code in.

both code block show perform poorly because of loops involved. set based approach problem this:

declare @newid int  select  top 1 @newid = rowid    (         select  id, row_number() on (order id) rowid            product         )   id <> rowid order rowid  select @newid 

tsql has weakness loops involved. in 99.9% of situations, better off avoiding loops.