SQL Server 2000 update records using function
Posted on 2011-09-19
Hi. I am trying to get an update statement to work in a SQL Server 2000 table using a function.
Table name: DSXref
Function name: GetRepsPixel
GetRepsPixel gets passed a string like ‘6-123’ and returns a larger string like ‘6-123-d1’.
The statement below shows the data I want from the function when I manually pass the string to the function.
select sampleid from GetRepsPixel('6-123') correctly returns ‘6-123-d1’
But I want to program the logic so I can pass a value like ‘6-123’ to the function in a variable that contains that value.
For example, I’d want to create working code for the pseudo code line below to work:
Update DataSummaryCouponXref set RepPixel = (select sampleid from GetRepsPixel(DataSummaryCouponXref field called CouponForDS))
In other words, I want to loop through the DataSummaryCouponXref table and update a field called RepPixel and set it to the value returned from GetRepsPixel when I pass a field called CouponForDS in that record to the function.
I hope this makes sense. It seems so simple but I can’t get it.