SQL Server - Issue with UPDATE inside while loop in a function? -

i have 1 table , willing fill 2 columns values generated using other column value using function.

note: working .mdf file in visual studio , not sql server.

like if employeename 'xyz' password 'xyz@123' , mailid 'xyz@gmail.com'

here procedure

create function [dbo].[fntempsetallemployeemailidandemployeepassword]() returns @outputtable table (     employeename nvarchar(250),     tempemployeemailid nvarchar(250),     tempemployeepassword nvarchar(250) ) begin     declare @initialiser int = 1, @numberofrowsintable int, @tempemployeeid int, @tempemployeename nvarchar(250);      select @numberofrowsintable = count(*)      tbemployee;      while(@initialiser <= @numberofrowsintable)     begin         select              @tempemployeename = [employeename],              @tempemployeeid = [employeeid]                      (select                   row_number() over(order [employeeid] asc) rownumber,                   [employeeid], [employeename]                                tbemployee) temptable                      rownumber = @initialiser;          update tbemployee          set [employeemailid] = lower(@tempemployeename) + '@gmail.com',              [employeepassword] = lower(@tempemployeename) + '@123'         [employeeid] = @tempemployeeid;          set @initialiser = @initialiser + 1;     end      insert @outputtable         select [employeename], [employeemailid], [employeepassword]          tbemployee;      return end 

the problem above statements works when execute in new query file.

but when put in function , try update it. not save , says went wrong when executing.

but saves when comment update command.

is problem update being in while loop?

there couple of things going on here.

firstly, reason doesn't work in function because in sql server functions cannot change in database. attempting change data in table , isn't allowed. allowed in stored procedure.

secondly, looks pretty inefficient way of doing update. each iteration of loop code:

1. grabs employees, sorts them
2. takes single row , updates it
3. inserts row table variable later output

as starting point, try updating every single row in table in 1 go:

create procedure dbo.tempsetallemployeemailidandemployeepassword begin     update tbemployee      set [employeemailid] = lower(@tempemployeename) + '@gmail.com',         [employeepassword] = lower(@tempemployeename) + '@123';      select employeename, employeemailid, employeepassword     tblemployee; end 

if turns out have problems because there many rows you're trying update @ once, maybe @ batching, that's separate topic.