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
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;
SET [EmployeeMailId] = LOWER(@TempEmployeeName) + '@gmail.com', [EmployeePassword] = LOWER(@TempEmployeeName) + '@123'
WHERE [EmployeeId] = @TempEmployeeId;
SELECT [EmployeeName], [EmployeeMailId], [EmployeePassword] FROM tbEmployee;
SET @Initialiser = @Initialiser + 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:
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.