sql server - How to write Batch SQL query in procedure -

if want write procedure below, there other way that, avoid using concatenate sql statement, afraid, if input long, exceed limit of max varchar, code have big problem.

thanks

create procedure update_all_status     @ids varchar(max) = null,     @status int = null begin     if @ids null     begin         return     end      declare @sql varchar(max)      set @sql = 'update mytable set status = ' + @status + ' id in (' + @ids + ')'     execute @sql end 

instead of dynamic sql (which vulnerable sql injection attacks) , passing in varchar(max), consider using table valued parameters:

-- creates tvp type - needed once! create type integertabletype table  ( identities int ); go  create procedure update_all_status     @ids integertabletype readonly,     @status int = null begin      update mytable      set status = @status     id in      (select identities @ids)  end 

this msdn article shows how call these .net code.