使用存储过程在数据库中插入值时自动生成id的问题 [英] problem in automatically generating id when inserting values in database using stored procedure

问题描述

我创建了一个只插入某些值的存储过程。到目前为止一切都很好，但我也希望列名称ID在数据库中输入内容时获取其值，即假设我插入：

i have created a stored procedure for only inserting certain values . Everything is fine till now but i also want that a column name ID gets its values when something is entered in database i.e suppose i insert :

execute spuserdetails 'Rachit','01-15-1988',null,'1 x free zone',97545,null,null,null

相应地，我不想通过SP填充的列自动填充自己像：

Rachit，1-15-1988，null，1 x free zone，97545，null，null，null， 1 现在我没有插入它但它生成了。

到目前为止我所做的是：

1.我已将我的ID列作为主键。

2. rt。点击该ID列 - >列支柱。 - >身份规范 - > isIdentity - >是。



通过SP ID发送值的对接显示为null。为什么？我错过了什么？在此先感谢。

than correspondingly a column that i dont want to fill by SP automatically fills itself like :
Rachit,1-15-1988,null,1 x free zone,97545,null,null,null,1 now i havenot inserted it but it gets generated .
What i have done so far is :
1. i have made my ID column as primary key.
2. rt. clicking on that ID column --> Column prop. --> Identity Specification --> isIdentity -->yes.

Butt on sending values via SP ID shows null.Why? what i m missing? Thanks in advance.

推荐答案

您好，



查看以下样本SP

Hi,

Check the below Sample SP

CREATE PROCEDURE Test
@EmpID INT,
@Name VARCHAR(100),
@ID INT OUTPUT
AS
BEGIN
	-- Insert Statement
	-- Table Structure (ID,EmpID,Name) here Identity Column is "ID"
	INSERT INTO Table_Name (EmpID,Name) VALUES(@EmpID, @Name)
	-- Get last Inserted Identity Value
	SELECT @ID=SCOPE_IDENTITY()
END

DECLARE @ID INT
EXEC Test 12,'Test', @ID=@ID OUTPUT
SELECT @ID

问候，

GVPRabu

Regards,
GVPRabu

您可以设置列属性



标识规范 - > （展开它）

（Is Identity）=是



您可以在此处设置增量值。默认设置为1.



通过更改此项，您无需传递此列的值，它将自动生成。

You can set the column Property by

Identification Specification -> (Expand it)
(Is Identity) = Yes

You can set the Increment Value here. By Default it is set to 1.

By changing this you need not to pass the Value for this column and it will be autogenerated.

我会继续做我做的事情，因为这是唯一真实的信息：

I'll go ahead and do what I'd do given this as the only real information:

execute spuserdetails 'Rachit','01-15-1988',null,'1 x free zone',97545,null,null,null

为表格提供随时可用的表格。

Make the table for what's ready-to-hand.

USE [cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_RS_UserDetails](
   [name][nvarchar](50),
      [date][datetime],
         [info_01][nvarchar](3),
             [location][nvarchar](13),
                [fivedigit][nvarchar](676),
                    [info_02][nvarchar](131),
                        [info_03][nvarchar](128),
                            [info_04][nvarchar](228)
    )

在我看不到的存储过程中，插入数据，确保目标是这个新的桌子。接下来制作另一张表。

In the stored procedure, that I can't see, INSERT the data making sure that the target is this newly crreated table. Next make another table.

CREATE TABLE [cpqa].[tbl_RS_UserDetailsIdx](
  [idx][int]IDENTITY(1,1),
   [name][nvarchar](50),
      [date][datetime],
         [info_01][nvarchar](3),
             [location][nvarchar](13),
                [fivedigit][nvarchar](676),
                    [info_02][nvarchar](131),
                        [info_03][nvarchar](128),
                            [info_04][nvarchar](228)
    )

在某个中间步骤中，在第一个表填充了要插入的记录后，执行相同的操作第二张桌子。索引是自动的。如果您尝试将数据放入不匹配的数据类型中，则会出现错误。

In an intermediate step somewhere, after the first table is filled with the records you want to INSERT, do the same thing with the second table. The indexing is automatic. If you try to put data in datatypes that mismatch you'll get an error.

这篇关于使用存储过程在数据库中插入值时自动生成id的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！