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

Question

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 .mdf file in Visual Studio and not SQL Server.

Like if EmployeeName is 'XYZ' then Password will be 'XYZ@123' and mailid will be 'XYZ@gmail.com'

Here is the procedure

CREATE FUNCTION [dbo].[fnTempSetAllEmployeeMailIdAndEmployeePassword]()
RETURNS @OutputTable TABLE
(
    EmployeeName NVARCHAR(250),
    TempEmployeeMailId NVARCHAR(250),
    TempEmployeePassword NVARCHAR(250)
)
AS
BEGIN
    DECLARE @Initialiser INT = 1, @NumberOfRowsInTable INT, @TempEmployeeId INT, @TempEmployeeName NVARCHAR(250);

    SELECT @NumberOfRowsInTable = COUNT(*) 
    FROM tbEmployee;

    WHILE(@Initialiser <= @NumberOfRowsInTable)
    BEGIN
        SELECT 
            @TempEmployeeName = [EmployeeName], 
            @TempEmployeeId = [EmployeeId]
        FROM 
            (SELECT 
                 ROW_NUMBER() OVER(ORDER BY [EmployeeId] ASC) AS ROwNumber, 
                 [EmployeeId], [EmployeeName]
             FROM 
                 tbEmployee) AS TempTable
        WHERE 
            RowNumber = @Initialiser;

        UPDATE tbEmployee 
        SET [EmployeeMailId] = LOWER(@TempEmployeeName) + '@gmail.com', 
            [EmployeePassword] = LOWER(@TempEmployeeName) + '@123'
        WHERE [EmployeeId] = @TempEmployeeId;

        SET @Initialiser = @Initialiser + 1;
    END

    INSERT @OutputTable
        SELECT [EmployeeName], [EmployeeMailId], [EmployeePassword] 
        FROM tbEmployee;

    RETURN
END

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

But when I put in function and try to update it. I will not save and says something went wrong when executing.

But saves when I comment the UPDATE command.

Is it problem with Update being in while loop?

Answer 1

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:

1. grabs all the employees, sorts them
2. takes a single row and updates it
3. inserts that row into a table variable to later output

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.