Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Looping through a SQL Server Table in UDF

I am writing a function (SQL Server 2000 UDF) that will go through one record at at a time then perform a procedure, then add the reult to a variable then go to next record. How do I advanve the function through the table records. This is urgent!!! Thanks,
0
tcknudson
Asked:
tcknudson
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
tcknudson,
> one record at at a time then perform a procedure

you can't call a procedure otherthab extended procedure in function
Better you write an sp
0
 
tcknudsonAuthor Commented:
In the sp, what is the syntax to move forward one record at a time? Do I try a loop?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can make use of a cursor
for example


create table #tab ( result int)  -- this will store the result of temp table
declare @procArgument int

declare cur cursor  for
select urRecord from urTable

OPEN cur
FETCH NEXT FROM cur INTO @procArgument
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #tab
    EXEC urStoredProcedure @procArgument  -- call ur sp

    FETCH NEXT FROM cur INTO @procArgument
END
CLOSE cur
DEALLOCATE cur

SELECT SUM(result) Result
FROM #Tab

DROP TABLE #tab
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now