how to use for loop in stored procedure sql server 2005

Question

i am new in SQL server. my requirement is I have to take thousands of records from c# as a XML file and from that file I am taking data in temp table. And from table I have to check record one by one if it is present then Update else insert. So I have written a stored procedure, but it is giving me the following error

Msg 102, Level 15, State 1, Procedure InsertIntoMyTable, Line 13
Incorrect syntax near 'cast'.
Msg 102, Level 15, State 1, Procedure InsertIntoMyTable, Line 18
Incorrect syntax near 'LOOP'.
Msg 156, Level 15, State 1, Procedure InsertIntoMyTable, Line 25
Incorrect syntax near the keyword 'END'.

Stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE InsertIntoMyTable
    @mytable xml 
 AS
BEGIN
    SELECT 
    cast(colx.query('data(PointsliceId) ') as int) as PointSliceId,
    cast(colx.query('data(Pt_timestamp) ') as datetime)     as Point_timestamp
    cast(colx.query('data(FloatValue) ') as float)     as Float_Value
INTo #TMP FROM @mytable.nodes('DocumentElement/mytable') AS Tabx(Colx)


For IDX in (select * from TMP)
LOOP
if((select count(*) from PointValue_Float where PointSliceId=IDX.PointSliceId and Pt_timeStamp=IDX.Pt_timeStamp)>0 )            
          update PointValue_Float set FloatValue=t.FloatValue from #TMP t  where t.PointSliceId=PointValue_Float.PointSliceId and t.Pt_timeStamp=PointValue_Float.Pt_timeStamp 
else
        insert into PointValue_Float(PointSliceId,Pt_timeStamp,FloatValue) SELECT PointSliceId,Pt_timeStamp,FloatValue FROM #TMP
END LOOP

END
GO

• My Table Name Is pointvalue_float where i have to check data is present or not if present then update else insert

Solution

Creating a manual loop in a SQL Server procedure is always a bad idea - SQL Server operates in sets of data - and your statement should also be set-oriented.

In your case, what I would do is this:

• shred the XML into a temporary table (as you already do)
• then update the existing values based on a join condition
• remove those rows updated from the temporary table
• the remaining rows need to be inserted

So your code would be something like:

CREATE PROCEDURE InsertIntoMyTable @mytable xml 
AS BEGIN
   SELECT 
      colx.value('(PointsliceId)[1]', 'INT') AS PointSliceId,
      colx.value('(Pt_timestamp)[1]', 'DATETIME') AS Point_timestamp
      colx.value('(FloatValue)[1]', 'FLAOT') AS Float_Value
   INTO #TMP 
   FROM @mytable.nodes('DocumentElement/mytable') AS Tabx(Colx)

-- udpate the existing rows
UPDATE dbo.PointValue_Float
SET FloatValue = t.FloatValue 
FROM #TMP t  
WHERE t.PointSliceId = PointValue_Float.PointSliceId 
  AND t.Pt_timeStamp = PointValue_Float.Pt_timeStamp 

-- remove those from the #TMP table
DELETE FROM #TMP
WHERE EXISTS
  (SELECT * FROM dbo.PointValue_Float 
   WHERE PointSliceId = #TMP.PointSliceId AND Pt_timeStamp = #TMP.Pt_timeStamp)

-- INSERT the remaining rows    
INSERT INTO 
    dbo.PointValue_Float(PointSliceId, Pt_timeStamp, FloatValue) 
  SELECT 
     PointSliceId, Pt_timeStamp, FloatValue 
  FROM #TMP
END

Comments

[Sachin Shanbhag]

@vishal - Please check my updated answer. Have linked to msdn article about the WHILE loop usage. Please go through that and modify your SP accordingly. This will help you understand instead of me giving the complete code.