如何使用存储过程插入多个数据 [英] How to insert mulitple datas using stored procedure

问题描述

hi
i希望在一个按钮上插入数据点击多个表格



i有像
这样的表格


1）custdetails

custid，custname，custadd，proid





2）proddetails

proid，prodname，价格



3）branddetails

proid，品牌名称



现在我想插入这三张桌子

使用商店程序如

hi i want to insert data on one button click in multiple tables

i have tables like

1) custdetails
custid,custname,custadd,proid


2)proddetails
proid,prodname,price

3)branddetails
proid,brandname

now i want to insert in this three table
in using a storeprocedure like

Create PROCEDURE [dbo].[InsertDetails_SP]
(
                @custid varchar(50),
                   @custname varchar(50),
                   @custadd varchar(200),
              @proid int identity (1,1) ,
               @prodname varchar (50),
               @price int ,
               @brandname varchar (100) ,
               
)
as
begin
set nocount on;
begin transaction
insert into custdetails
(
custid,custname,custadd,proid
)
values
(
@custid,@custname,@custadd,proid
)
Declare @proid int 
set @proid=SCOPE_IDENTITY()
If @@ERROR=0
Begin
insert into proddetails
(
proid,prodname,price
)
values
(
@proid,@prodname,@price
)
end
if @@Error=0
begin
insert into branddetails(
proid,brandname
)
values
(
@proid,@brandname
)
IF @@ERROR = 0
				BEGIN			 
					COMMIT TRANSACTION
				END
			ELSE
				BEGIN			 
					ROLLBACK TRANSACTION		
				END
		END
ELSE
		BEGIN
			ROLLBACK TRANSACTION
		END
 
END

但是我得到的错误是必须声明@brandname的标量变量





请帮助我解决此错误...

But am getting error that Must declare scalar variable for @brandname


please some one could you help me resolve this error...

推荐答案

我看到几个错误修正错误，这里是代码。

我看到的几个错误

- int身份传递作为输入参数

- 最后一个输入参数后的逗号

- 插入语句中的proid（no @）

- @proid的声明多次



你现在可以充电了sp没有任何问题，但你需要查看你已经正确编写所有内容的脚本。



检查行 - 设置@proid = SCOPE_IDENTITY（）

看起来不正确

Hi,

I see several errors Fixed the error and here is the code.
few errors I see
- int identity passing as input parameter
- comma after the last input parameter
- proid in insert statement ( no @)
- declaration of @proid multiple times

You can now charge the sp without any issue, but you need to look into the scrip that you have written everything correctly.

check the line - set @proid=SCOPE_IDENTITY()
looks incorrect

Create PROCEDURE [dbo].[InsertDetails_SP]
(
              @custid varchar(50),
              @custname varchar(50),
              @custadd varchar(200),
              @proid int,
              @prodname varchar (50),
              @price int ,
              @brandname varchar (100)
)
as
begin
set nocount on;
begin transaction
insert into custdetails
(
custid,custname,custadd,proid
)
values
(
@custid,@custname,@custadd,@proid
)
--Declare @proid int 
set @proid=SCOPE_IDENTITY()
If @@ERROR=0
Begin
insert into proddetails
(
proid,prodname,price
)
values
(
@proid,@prodname,@price
)
end
if @@Error=0
begin
insert into branddetails(
proid,brandname
)
values
(
@proid,@brandname
)
IF @@ERROR = 0
				BEGIN			 
					COMMIT TRANSACTION
				END
			ELSE
				BEGIN			 
					ROLLBACK TRANSACTION		
				END
		END
ELSE
		BEGIN
			ROLLBACK TRANSACTION
		END
 
END

希望这有帮助

Hope this helps

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