Execute Store Procedure select row by row

Question

Hello, 

I'm try to create a stored procedure to convert the bigint number in the datetime.
I'm the using the next code to execute the task..


use Proximity
go
drop proc dbo.ConvertTimeStamp
go
CREATE PROCEDURE dbo.ConvertTimeStamp
AS
DECLARE @LocalTime BIGINT,@Adjusted_time BIGINT;
    DECLARE @T_STAMP BIGINT;
    DECLARE @TIME DATETIME;
    
    SELECT @T_STAMP = TIME_STAMP FROM dbo.comutation
    print @T_STAMP
    WHILE @T_STAMP IS NOT NULL
BEGIN
SET @LocalTime = DATEDIFF(second,GETDATE(),GETUTCDATE())
SET @Adjusted_time = @T_STAMP - @LocalTime
SET @TIME=DATEADD(second,@Adjusted_time, CAST('1970-01-01 00:00:00' AS datetime))

Update dbo.comutation
SET DateHour=@TIME
where TIME_STAMP=@T_STAMP
End



exec dbo.ConvertTimeStamp



The my problem is to read row to row the table, and don't understand because not update all lines the table only update the first row.

thanks

Answer 1

You will need a cursor, or similar approach, to traverse the rows of a table one at a time, but I do not see the needs for this in your case.

CREATE PROCEDURE dbo.ConvertTimeStamp
AS
SET NOCOUNT ON;

DECLARE @LocalTime BIGINT;

SET @LocalTime = DATEDIFF(second,GETDATE(),GETUTCDATE());

UPDATE dbo.comutation
SET DateHour = DATEADD(second, (TIME_STAMP - @LocalTime), '19700101');
GO

Feel free to add error handling.

---

AMB

Some guidelines for posting questions...

Answer 2

You try to manipulate a set as if you are programming in a declarative language like C# or .NET.

Try this

DECLARE @LocalTime BIGINT;
SET @LocalTime = DATEDIFF(second,GETDATE(),GETUTCDATE());
UPDATE dbo.comutation
SET    DateHour = DATEADD(second,T_STAMP - @LocalTime, CAST('1970-01-01 00:00:00' AS datetime);

Answer 3

Hi Hunchback and Ricardo,

I'm sorry for my duplicate post, but there really was no reply yet when I started hammering on my keyboard.
I'm 100% sure that Hunchback's solution is the right one. So, Ricardo, if it works, please mark that reply as answer and not mine.