如何编写存储过程？ [英] How to write stored procedure?

问题描述

我有一个名为'Map'的表，列为ids和shift。那么我对存储过程的输入值是'ids'和'shift'。



例如：

ids = 106,101,200

shift = 1





然后我想检查表Map中已经存在的每个id。如果它存在则删除其他



在for循环中添加每个id和shift值。表格如下所示。

Hi,


I have a table named 'Map' with column as ids and shift. then my input values to the stored procedure are 'ids' and 'shift'.

example:
ids=106,101,200
shift=1


Then i want to check each id already exists in table Map. If it exist then delete else

add each id and shift value in a for loop. the table look as below.

ids       shift
---       -----
106         1
101         1
200         1

谢谢

Thanks

推荐答案

您一次只能添加1行，因此在循环中您必须使用必要的参数调用存储过程：

阅读这个 [ ^ ]如何创建存储过程。

You can only add 1 row at a time so in the Loop you have to call the stored procedure with the neccessary parameters:
Read This[^] on how to create a Stored Procedure.

如果您将shift和ID集合转换为XML字符串，则可以将其传递给存储的pro c并使用select from xml来插入数据。我会使用临时表来获取ID，以便您可以删除现有的ID。



搜索在SQL Server中插入多个条目或插入xml。

If you turn your collection of shift and ID into an XML string then you can pass that to the stored proc and use select from xml to insert the data. I would use a temp table to get at the IDs so you can delete the existing ones.

Do a search for inserting multiple entries into SQL Server or inserting xml.

没有什么难以做到的。你应该先试试。



骨架总是一样的;）

There is nothing hard to do. You should try first.

The skeleton is always the same ;)

CREATE PROCEDURE ProcedureName
    @ids VARCHAR(30),
    @shift INT
AS
BEGIN
--the body of the procedure
END

我想知道为什么要删除记录而不是更新现有记录。我建议你重新考虑你项目的这一部分。



看看例子：

I was wondering why do you want to delete records rather than update existing ones... I would suggest you to re-think this part of your project.

Have a look at example:

--MAP table
DECLARE @map TABLE (ID INT, Shift INT)
--example data
INSERT INTO @map (ID, Shift)
SELECT 106, 1
UNION ALL SELECT 101, 1
UNION ALL SELECT 200, 1
UNION ALL SELECT 121, 1
UNION ALL SELECT 220, 1
UNION ALL SELECT 122, 1
UNION ALL SELECT 222, 1
UNION ALL SELECT 301, 1
UNION ALL SELECT 203, 1
UNION ALL SELECT 103, 1
UNION ALL SELECT 233, 1

--show example data; uncomment below lines to show result
--SELECT *
--FROM @map
--ORDER BY ID

--collection of id's separated by comma
--in your case it would be a parameter of SP
DECLARE @ids VARCHAR(30)

SET @ids = '106,101,200'

--temporary table
DECLARE @tmp TABLE (ID INT)
--get id's from varchar variable and insert into temporary table
;WITH IDCollection AS
(
	SELECT CONVERT(INT, LEFT(@ids, CHARINDEX(',', @ids)-1)) AS ID, RIGHT(@ids,LEN(@ids) - CHARINDEX(',', @ids)) AS Remainder
	WHERE CHARINDEX(',', @ids)>0
	UNION ALL
	SELECT CONVERT(INT,LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS ID, RIGHT(Remainder,LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM IDCollection
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT CONVERT(INT,Remainder) AS ID, NULL AS Remainder
	FROM IDCollection
	WHERE CHARINDEX(',', Remainder)=0
)
INSERT INTO @tmp (ID)
SELECT ID
FROM IDCollection

--delete data based on passed id's
DELETE 
FROM @map 
WHERE ID IN(SELECT ID FROM @tmp)

--insert new data
INSERT INTO @map (ID, Shift) 
SELECT ID, 1 AS Shift
FROM @tmp 

--show data 
SELECT *
FROM @map

正如我上面提到的，更好的方法是更新现有记录，而不是删除和添加新记录。

As i mentioned above, better way is to update existing records instead deleting and adding new ones.

UPDATE @map SET Shift = @Shift
WHERE ID IN (SELECT ID FROM @tmp)

这篇关于如何编写存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！