SQL存储过程 - 帮我写这一个，请！ [英] SQL stored proc - help me write this one, please!

问题描述

您能帮我一下吗？

在我的一个脚本中，我在一些表Item中插入了一些数据。 Item表中的一个列是ItemNumber。

In one of my scripts I am inserting some data in to some table "Item". One of the columns in the "Item" table is "ItemNumber".

我想能够调用一些函数（很可能是一个Stored proc），它会返回一个数字，我可以使用ItemNumber。

I want to be able to call some function (most probably a Stored proc) where it would return me a numeric number which I can use for the ItemNumber.

我不能使用AutoIncrement，因为ItemNumber是唯一的，并且在一个不同的脚本中可能存在冲突我在Item中插入数据，已经有ItemNumber

I CAN NOT use AutoIncrement because ItemNumber is unique and there might be a collision when in a differnt script I am inserting data in "Item", which already has "ItemNumber"

我只知道ItemNumber的最大数字，我可以随后使用任何内容。

I only know the Max number for the "ItemNumber" and I am free to use anything after that.

我需要一个表来存储下一个要使用的数字，对吗？

I need a table for this storedProc to store the next number to be used, right?

我想要创建一个带有MaxNumber在它和一个storedproc，它会返回我的MaxNumber ，并增加1的下一次使用的MaxNumber

I was thinking of creating a one column table with the MaxNumber in it and a storedproc where it would return me the MaxNumber and also increment the MaxNumber by 1 for the next use

我需要帮助编写存储过程及其在 INSERT INTO Item 调用中的使用。

I need help writing the stored proc and it usage with in the INSERT INTO Item call.

基本上，我从5个旧表中获取数据，其中ItemNumber为NULL或设置。对于设置的那些，它们都是唯一的数字。
对于ItemNumber为NULL的数据我想分配它，最好的方法是什么？

Basically I am grabbing data from 5 old tables where ItemNumber is either NULL or set. For the ones which are set they are all unique numbers. For the data where the ItemNumber is NULL I want to assign it, what the best approach?

帮助请！

谢谢，

Voodoo

推荐答案

假设SQL Server - 可能适用于大多数SQL实现）

(Assuming SQL Server - probably applies to most SQL implementations)

如果您对字段使用IDENTITY约束，服务器将不允许存在重复实例。作为注释之一，在这种情况下，在服务器上计算该值，并且您的应用程序不会（并且在正常情况下不能提供）。

If you use IDENTITY constraint on your field, the server will not allow there to be duplicate instances. As one of the comments notes, the value is calculated on the server in this case, and your application does not (and under normal circumstances cannot) provide it.

服务器不保证数字将是连续的 - 如果你做一个插入，并中止事务，将使用的值将被丢失 - 服务器将不再提供它。

Note that the server does not guarantee that the numbers will be contiguous - should you do an insert, and abort the transaction, the value that would have been used would be 'lost' - the server will not offer it again.

如果您需要具有连续的ID号，您将需要具有serlialised访问权限来更新表，您需要管理这些表，这将不会有效。 （您仍然可以将其声明为IDENTITY列，但需要为每个更新的表设置IDENTITY_INSERT On。）

If you need to have contiguous ID numbers, you will need to have serlialised access to update the table, which you would need to manage and which would not be efficient. (You can still declare it to be an IDENTITY column, but would need to set IDENTITY_INSERT On for the table for each update.)

其他：

可能有点污点，但可能适用于一次性的情况。
您是否考虑将数据复制到新表中，其中设置了ID，然后应用IDENTITY约束，并插入带有NULL ID的表（允许服务器为您分配它们）
可能会在这里解决您的具体问题。

Possibly a bit of a kludge, but might work for a one-off scenario. Have you considered copying the data into a new table, where the ID is set, then applying the IDENTITY constraint, and inserting the ones with NULL IDs (allowing the server to allocate them for you?) That might fix your specific issue here.

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