如何在存储过程中使用for循环SQL Server 2005

Question

I am new to SQL Server and my task is to extract thousands of records from C# as an XML file, then import the data into a temp table. After that, I need to check each record one by one and update it if it exists, otherwise insert it. To achieve this, I have written a stored procedure. However, I encountered 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'.

Here is the 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

• 我的表名是pointvalue_float，我需要检查数据是否存在，如果存在则更新，否则插入。

- vishal

---

哦，Fox Pro 的 scan...endscan 那些美好的日子啊！有时候我真希望它们永远不会过去。 - Andriy M

Answer 1

在 SQL Server 存储过程中手动循环是一个不好的想法 - SQL Server 操作数据的时候是基于数据集的，你的语句也应该是面向数据集的。

在你的情况下，我会这样做：

• 将 XML 数据分解到临时表中（就像你已经做的一样）
• 然后根据连接条件更新现有值
• 从临时表中删除那些已更新的行
• 剩下的行需要被插入

因此，你的代码应该是这样的：

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

- marc_s

Answer 2

1. 你在第二行（case语句）末尾缺少逗号。选择的字段必须始终用逗号分隔。在你的情况下，第二个case语句中，行末的逗号缺失了。
2. 你必须使用BEGIN和END来开始和结束WHILE循环。在存储过程中最好使用WHILE而不是FOR。不确定SQL Server存储过程中是否有类似于FOR的东西。

- Sachin Shanbhag

---

@vishal - 请检查我的更新答案。我已经链接到关于WHILE循环用法的MSDN文章。请仔细阅读并相应修改您的存储过程。这将帮助您理解，而不是我给出完整的代码。 - Sachin Shanbhag