how to use for loop in stored procedure sql server 2005

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

---

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

---

You are missing a comma on second line (case statement) at the end. The select fields must always be seperated by a comma. In your case, the second case statement, the comma at end of line is missing.

You have to use BEGIN and END for starting and ending WHILE loop. WHILE is better to be used in stored procedures instead of FOR. Not sure if there is something like FOR in sql server stored procedures.

链接地址: http://www.djcxy.com/p/24746.html

上一篇: 如何重命名SQL Server中的表？

下一篇: 如何在存储过程sql server 2005中使用循环