Nithin B Nithin B - 1 year ago 62
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 ''

Here is the procedure

CREATE FUNCTION [dbo].[fnTempSetAllEmployeeMailIdAndEmployeePassword]()
EmployeeName NVARCHAR(250),
TempEmployeeMailId NVARCHAR(250),
TempEmployeePassword NVARCHAR(250)
DECLARE @Initialiser INT = 1, @NumberOfRowsInTable INT, @TempEmployeeId INT, @TempEmployeeName NVARCHAR(250);
SELECT @NumberOfRowsInTable = COUNT(*) FROM tbEmployee;
WHILE(@Initialiser <= @NumberOfRowsInTable)
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) + '', [EmployeePassword] = LOWER(@TempEmployeeName) + '@123'
WHERE [EmployeeId] = @TempEmployeeId;

INSERT @OutputTable
SELECT [EmployeeName], [EmployeeMailId], [EmployeePassword] FROM tbEmployee;
SET @Initialiser = @Initialiser + 1;

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 Source

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
    UPDATE tbEmployee 
       SET [EmployeeMailId] = LOWER(@TempEmployeeName) + '',
        [EmployeePassword] = LOWER(@TempEmployeeName) + '@123';

    SELECT EmployeeName, EmployeeMailID, EmployeePassword
      FROM tblEmployee;

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download