how to loop over results of a select statement ?
-
I have a select query that returns a some rows . i want to send each of the rows in returned result to a function but i do not know how ? can you help me "Note that i do not want to use Cursor "
select PersonelCode , ...
from PersonnelTable
and the function Need the PersonelCode.For each row this function should be executed
Create Function MyFunction ( @PersonelCode )
begin
body of the function
end -
I have a select query that returns a some rows . i want to send each of the rows in returned result to a function but i do not know how ? can you help me "Note that i do not want to use Cursor "
select PersonelCode , ...
from PersonnelTable
and the function Need the PersonelCode.For each row this function should be executed
Create Function MyFunction ( @PersonelCode )
begin
body of the function
endHi, Check the following Script, It will be use full for ur Query..........
SET NOCOUNT ON
-- Declaration
DECLARE @Employee TABLE(ID INT , EmpName VARCHAR(40))
DECLARE @I INT, @Count INT, @Temp VARCHAR(50)SET @I = 1
-- Data
INSERT INTO @Employee(ID,EmpName)
SELECT 123,'Prabu'
UNION ALL SELECT 234, 'Raja'
UNION ALL SELECT 236, 'Kartik'
UNION ALL SELECT 1234, 'Venkat'-- TSQL Script
SELECT @Count=COUNT(*) FROM @Employee
PRINT '~'
PRINT 'Employee Details'
PRINT '
WHILE @I <= @Count
BEGIN
WITH Emp AS
(
SELECT row_number() OVER ( ORDER BY ID ) 'RowNum', ID, EmpName
FROM @Employee
)
SELECT @Temp =CAST(ID AS VARCHAR) +' - '+EmpName
FROM Emp
WHERE RowNum =@I
PRINT @Temp
SET @I=@I+1
END
PRINT '~~~~~~~~~~~~~~~~~'
SET NOCOUNT OFF -
I have a select query that returns a some rows . i want to send each of the rows in returned result to a function but i do not know how ? can you help me "Note that i do not want to use Cursor "
select PersonelCode , ...
from PersonnelTable
and the function Need the PersonelCode.For each row this function should be executed
Create Function MyFunction ( @PersonelCode )
begin
body of the function
end