Nithin B Nithin B - 23 days ago 9
SQL Question

SQL SERVER - Issue with UPDATE inside while loop in a sql server function?

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 an .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;

INSERT @OutputTable
SELECT [EmployeeName], [EmployeeMailId], [EmployeePassword] FROM tbEmployee;
SET @Initialiser = @Initialiser + 1;
END
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

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.