I have one table and I am willing to fill two columns with values generated using other column value using function.
NOTE: I am working with a
CREATE FUNCTION [dbo].[fnTempSetAllEmployeeMailIdAndEmployeePassword]()
RETURNS @OutputTable TABLE
DECLARE @Initialiser INT = 1, @NumberOfRowsInTable INT, @TempEmployeeId INT, @TempEmployeeName NVARCHAR(250);
SELECT @NumberOfRowsInTable = COUNT(*)
WHILE(@Initialiser <= @NumberOfRowsInTable)
@TempEmployeeName = [EmployeeName],
@TempEmployeeId = [EmployeeId]
ROW_NUMBER() OVER(ORDER BY [EmployeeId] ASC) AS ROwNumber,
tbEmployee) AS TempTable
RowNumber = @Initialiser;
SET [EmployeeMailId] = LOWER(@TempEmployeeName) + '@gmail.com',
[EmployeePassword] = LOWER(@TempEmployeeName) + '@123'
WHERE [EmployeeId] = @TempEmployeeId;
SET @Initialiser = @Initialiser + 1;
SELECT [EmployeeName], [EmployeeMailId], [EmployeePassword]
There are a couple of things going on here.
Firstly, the reason that it doesn't work in a function is because in SQL Server functions cannot change anything in the database. You are attempting to change the data in the table and that isn't allowed. It would be allowed in a stored procedure.
Secondly, it looks like a pretty inefficient way of doing the update. For each iteration of the loop this code:
As a starting point, try just updating every single row in the table in one go:
CREATE PROCEDURE dbo.TempSetAllEmployeeMailIdAndEmployeePassword AS BEGIN UPDATE tbEmployee SET [EmployeeMailId] = LOWER(@TempEmployeeName) + '@gmail.com', [EmployeePassword] = LOWER(@TempEmployeeName) + '@123'; SELECT EmployeeName, EmployeeMailID, EmployeePassword FROM tblEmployee; END
If it turns out that you have problems because there are too many rows that you're trying to update at once, then maybe you could look at batching, but that's probably a separate topic.