在存储过程中定义一个输出参数 @outID int output 然后执行操作后 set @outID=@@identity 调用的时候,就可以得到输出参数了!
或者begin transaction declare @outID int .... insert into ... .... set @outID=@@identity commit transaction return @@outID----------调用----------- declare @ID int exec @ID=p_test print @ID
不行啊。 我是这样写的。帮我看看 @newid int output begin transaction ... insert into... set @newid=@@identity ..... commit transaction return @newid 可是提示必须定义变量@newid 我换成declare @newid int output 也是这个提示。 明天就毕业设计答辩了,急,请帮忙解决一下。
create proc udt_order @re int output as begin transaction .... insert into ... set @re=@@identity --插入后即取.... commit transaction go--调用 declare @re int exec udt_order @re out select 结果=@re
同意zjcxc(邹建)兄的,如果要返回值的话,用我后来写的,肯定行!
豁出去拉,不怕丢人。我把代码贴上来了。帮我看看,不行啊,我没系统学过SQL SERVER啊~~~~ ----------------------方法(1)其他人的---------------------- CREATE PROCEDURE udt_orderBk @pUID varchar(15), @pPWD varchar(15), @pOrderName varchar(30), @pOrderAddress varchar(100), @pOrderZIP char(6), @pPhone_p varchar(20), @pPhone_s varchar(20), @pEmail varchar(30), @pVirtMoney money, @pOrderIP char(15) AS /*@NEWID INT OUTPUT*/ IF (NOT EXISTS( SELECT * FROM tMemberList WHERE LoginName=@pUID AND LoginPWD=@pPWD)) BEGIN RETURN -1 END ELSE BEGIN BEGIN TRANSACTION INSERT INTO tOrdersList(LoginName,CustomerName,CustomerAddress, CustomerZIP,CustomerPhone_p,CustomerPhone_s, CustomerEmail,VirtMoney,CustomerIP) VALUES(@pUID,@pOrderName,@pOrderAddress,@pOrderZIP, @pPhone_p,@pPhone_s,@pEmail,@pVirtMoney,@pOrderIP) INSERT INTO tOrderDetails SELECT @@IDENTITY,BCode,BQuantity,BMem_Price,BDiscount FROM vBook_Shopcart WHERE ULoginName=@pUID /*SET @NEWID=@@IDENTITY*/ DECLARE @ttlPrice money SELECT @ttlPrice=(SELECT (SUM(BTotal_Price)-@pVirtMoney) AS ttlPrice FROM vBook_Shopcart WHERE ULoginName=@pUID) UPDATE tBookStocks SET BStocks=T1.BStocks-T2.BQuantity FROM tBookStocks AS T1,tShopcartList AS T2 WHERE T1.BCode=T2.BCode AND T2.ULoginName=@pUID DELETE FROM tShopcartList WHERE ULoginName=@pUID UPDATE tMemberList SET PaidPoint=PaidPoint+@pVirtMoney, VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.05 WHERE LoginName=@pUID UPDATE tMemberList SET PaidPoint=PaidPoint+@pVirtMoney, VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.01 WHERE LoginName=(SELECT RefBy FROM tMemberList WHERE LoginName=@pUID) IF (@@ERROR = 0) BEGIN COMMIT TRANSACTION SELECT @@IDENTITY END ELSE BEGIN ROLLBACK TRANSACTION RETURN -2 END END GO ------------------------------------------------ --------------------方法(2)飞的,邹建的------------------- CREATE PROCEDURE udt_orderBk @pUID varchar(15), @pPWD varchar(15), @pOrderName varchar(30), @pOrderAddress varchar(100), @pOrderZIP char(6), @pPhone_p varchar(20), @pPhone_s varchar(20), @pEmail varchar(30), @pVirtMoney money, @pOrderIP char(15) AS *********************************** @NEWID INT OUTPUT *********************************** IF (NOT EXISTS( SELECT * FROM tMemberList WHERE LoginName=@pUID AND LoginPWD=@pPWD)) BEGIN RETURN -1 END ELSE BEGIN BEGIN TRANSACTION INSERT INTO tOrdersList(LoginName,CustomerName,CustomerAddress, CustomerZIP,CustomerPhone_p,CustomerPhone_s, CustomerEmail,VirtMoney,CustomerIP) VALUES(@pUID,@pOrderName,@pOrderAddress,@pOrderZIP, @pPhone_p,@pPhone_s,@pEmail,@pVirtMoney,@pOrderIP) INSERT INTO tOrderDetails SELECT @@IDENTITY,BCode,BQuantity,BMem_Price,BDiscount FROM vBook_Shopcart WHERE ULoginName=@pUID ************************************************* SET @NEWID=@@IDENTITY ************************************************* DECLARE @ttlPrice money SELECT @ttlPrice=(SELECT (SUM(BTotal_Price)-@pVirtMoney) AS ttlPrice FROM vBook_Shopcart WHERE ULoginName=@pUID) UPDATE tBookStocks SET BStocks=T1.BStocks-T2.BQuantity FROM tBookStocks AS T1,tShopcartList AS T2 WHERE T1.BCode=T2.BCode AND T2.ULoginName=@pUID DELETE FROM tShopcartList WHERE ULoginName=@pUID UPDATE tMemberList SET PaidPoint=PaidPoint+@pVirtMoney, VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.05 WHERE LoginName=@pUID UPDATE tMemberList SET PaidPoint=PaidPoint+@pVirtMoney, VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.01 WHERE LoginName=(SELECT RefBy FROM tMemberList WHERE LoginName=@pUID) ***************************************** IF (@@ERROR = 0) COMMIT TRANSACTION ELSE BEGIN ROLLBACK TRANSACTION RETURN -2 END ***************************************** END GO ----------------------------------------------------- 大概就是这么回事拉,我是实在不行拉。
CREATE PROCEDURE udt_orderBk @pUID varchar(15), @pPWD varchar(15), @pOrderName varchar(30), @pOrderAddress varchar(100), @pOrderZIP char(6), @pPhone_p varchar(20), @pPhone_s varchar(20), @pEmail varchar(30), @pVirtMoney money, @pOrderIP char(15), @NEWID INT OUTPUT -----**位置放错了AS --*********************************** --@NEWID INT OUTPUT --*********************************** IF (NOT EXISTS( SELECT * FROM tMemberList WHERE LoginName=@pUID AND LoginPWD=@pPWD)) BEGIN RETURN -1 END ELSE BEGIN BEGIN TRANSACTION INSERT INTO tOrdersList(LoginName,CustomerName,CustomerAddress, CustomerZIP,CustomerPhone_p,CustomerPhone_s, CustomerEmail,VirtMoney,CustomerIP) VALUES(@pUID,@pOrderName,@pOrderAddress,@pOrderZIP,
@pPhone_p,@pPhone_s,@pEmail,@pVirtMoney,@pOrderIP) INSERT INTO tOrderDetails SELECT @@IDENTITY,BCode,BQuantity,BMem_Price,BDiscount FROM vBook_Shopcart WHERE ULoginName=@pUID --************************************************* SET @NEWID=@@IDENTITY --你是要得到插入那个表的identity?????--************************************************* DECLARE @ttlPrice money SELECT @ttlPrice=(SELECT (SUM(BTotal_Price)-@pVirtMoney) AS ttlPrice FROM vBook_Shopcart WHERE ULoginName=@pUID) UPDATE tBookStocks SET BStocks=T1.BStocks-T2.BQuantity FROM tBookStocks AS T1,tShopcartList AS T2 WHERE T1.BCode=T2.BCode AND T2.ULoginName=@pUID DELETE FROM tShopcartList WHERE ULoginName=@pUID UPDATE tMemberList SET PaidPoint=PaidPoint+@pVirtMoney, VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.05 WHERE LoginName=@pUID UPDATE tMemberList SET PaidPoint=PaidPoint+@pVirtMoney, VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.01 WHERE LoginName=(SELECT RefBy FROM tMemberList WHERE LoginName=@pUID) --***************************************** IF (@@ERROR = 0) COMMIT TRANSACTION ELSE BEGIN ROLLBACK TRANSACTION RETURN -2 END --***************************************** END GO
CREATE PROCEDURE udt_orderBk @pUID varchar(15), @pPWD varchar(15), @pOrderName varchar(30), @pOrderAddress varchar(100), @pOrderZIP char(6), @pPhone_p varchar(20), @pPhone_s varchar(20), @pEmail varchar(30), @pVirtMoney money, @pOrderIP char(15) ASdeclare @NEWID INT -- OUTPUT --加declare,不要outputIF (NOT EXISTS( SELECT * FROM tMemberList WHERE LoginName=@pUID AND LoginPWD=@pPWD)) BEGIN RETURN -1 END ELSE BEGIN BEGIN TRANSACTION INSERT INTO tOrdersList(LoginName,CustomerName,CustomerAddress, CustomerZIP,CustomerPhone_p,CustomerPhone_s, CustomerEmail,VirtMoney,CustomerIP) VALUES(@pUID,@pOrderName,@pOrderAddress,@pOrderZIP, @pPhone_p,@pPhone_s,@pEmail,@pVirtMoney,@pOrderIP) INSERT INTO tOrderDetails SELECT @@IDENTITY,BCode,BQuantity,BMem_Price,BDiscount FROM vBook_Shopcart WHERE ULoginName=@pUID SET @NEWID=@@IDENTITY DECLARE @ttlPrice money SELECT @ttlPrice=(SELECT (SUM(BTotal_Price)-@pVirtMoney) AS ttlPrice FROM vBook_Shopcart WHERE ULoginName=@pUID) UPDATE tBookStocks SET BStocks=T1.BStocks-T2.BQuantity FROM tBookStocks AS T1,tShopcartList AS T2 WHERE T1.BCode=T2.BCode AND T2.ULoginName=@pUID DELETE FROM tShopcartList WHERE ULoginName=@pUID UPDATE tMemberList SET PaidPoint=PaidPoint+@pVirtMoney, VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.05 WHERE LoginName=@pUID UPDATE tMemberList SET PaidPoint=PaidPoint+@pVirtMoney, VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.01 WHERE LoginName=(SELECT RefBy FROM tMemberList WHERE LoginName=@pUID) IF (@@ERROR = 0) BEGIN COMMIT TRANSACTION -- SELECT @@IDENTITY return @NEWID END ELSE BEGIN ROLLBACK TRANSACTION RETURN -2 END END GO
因为我的insert into表的id字段设为自动+1
用select @@identity是有值的。
我怀疑可能是包含在事务内部,所以事务外部的是null,可是怎么返回@@identity呢??
@outID int output
然后执行操作后
set @outID=@@identity
调用的时候,就可以得到输出参数了!
declare @outID int
....
insert into ...
....
set @outID=@@identity
commit transaction
return @@outID----------调用-----------
declare @ID int
exec @ID=p_test
print @ID
我是这样写的。帮我看看
@newid int output
begin transaction
...
insert into...
set @newid=@@identity
.....
commit transaction
return @newid
可是提示必须定义变量@newid
我换成declare @newid int output
也是这个提示。
明天就毕业设计答辩了,急,请帮忙解决一下。
@re int output
as
begin transaction
....
insert into ...
set @re=@@identity --插入后即取....
commit transaction
go--调用
declare @re int
exec udt_order @re out
select 结果=@re
----------------------方法(1)其他人的----------------------
CREATE PROCEDURE udt_orderBk
@pUID varchar(15),
@pPWD varchar(15),
@pOrderName varchar(30),
@pOrderAddress varchar(100),
@pOrderZIP char(6),
@pPhone_p varchar(20),
@pPhone_s varchar(20),
@pEmail varchar(30),
@pVirtMoney money,
@pOrderIP char(15)
AS
/*@NEWID INT OUTPUT*/
IF (NOT EXISTS(
SELECT *
FROM tMemberList
WHERE LoginName=@pUID AND LoginPWD=@pPWD))
BEGIN
RETURN -1
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tOrdersList(LoginName,CustomerName,CustomerAddress,
CustomerZIP,CustomerPhone_p,CustomerPhone_s,
CustomerEmail,VirtMoney,CustomerIP)
VALUES(@pUID,@pOrderName,@pOrderAddress,@pOrderZIP,
@pPhone_p,@pPhone_s,@pEmail,@pVirtMoney,@pOrderIP)
INSERT INTO tOrderDetails
SELECT @@IDENTITY,BCode,BQuantity,BMem_Price,BDiscount
FROM vBook_Shopcart
WHERE ULoginName=@pUID
/*SET @NEWID=@@IDENTITY*/
DECLARE @ttlPrice money
SELECT @ttlPrice=(SELECT (SUM(BTotal_Price)-@pVirtMoney) AS ttlPrice
FROM vBook_Shopcart
WHERE ULoginName=@pUID)
UPDATE tBookStocks SET BStocks=T1.BStocks-T2.BQuantity
FROM tBookStocks AS T1,tShopcartList AS T2
WHERE T1.BCode=T2.BCode AND T2.ULoginName=@pUID
DELETE FROM tShopcartList WHERE ULoginName=@pUID
UPDATE tMemberList
SET PaidPoint=PaidPoint+@pVirtMoney,
VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.05
WHERE LoginName=@pUID
UPDATE tMemberList
SET PaidPoint=PaidPoint+@pVirtMoney,
VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.01
WHERE LoginName=(SELECT RefBy
FROM tMemberList
WHERE LoginName=@pUID)
IF (@@ERROR = 0)
BEGIN
COMMIT TRANSACTION
SELECT @@IDENTITY
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
GO
------------------------------------------------
--------------------方法(2)飞的,邹建的-------------------
CREATE PROCEDURE udt_orderBk
@pUID varchar(15),
@pPWD varchar(15),
@pOrderName varchar(30),
@pOrderAddress varchar(100),
@pOrderZIP char(6),
@pPhone_p varchar(20),
@pPhone_s varchar(20),
@pEmail varchar(30),
@pVirtMoney money,
@pOrderIP char(15)
AS
***********************************
@NEWID INT OUTPUT
***********************************
IF (NOT EXISTS(
SELECT *
FROM tMemberList
WHERE LoginName=@pUID AND LoginPWD=@pPWD))
BEGIN
RETURN -1
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tOrdersList(LoginName,CustomerName,CustomerAddress,
CustomerZIP,CustomerPhone_p,CustomerPhone_s,
CustomerEmail,VirtMoney,CustomerIP)
VALUES(@pUID,@pOrderName,@pOrderAddress,@pOrderZIP,
@pPhone_p,@pPhone_s,@pEmail,@pVirtMoney,@pOrderIP)
INSERT INTO tOrderDetails
SELECT @@IDENTITY,BCode,BQuantity,BMem_Price,BDiscount
FROM vBook_Shopcart
WHERE ULoginName=@pUID
*************************************************
SET @NEWID=@@IDENTITY
*************************************************
DECLARE @ttlPrice money
SELECT @ttlPrice=(SELECT (SUM(BTotal_Price)-@pVirtMoney) AS ttlPrice
FROM vBook_Shopcart
WHERE ULoginName=@pUID)
UPDATE tBookStocks SET BStocks=T1.BStocks-T2.BQuantity
FROM tBookStocks AS T1,tShopcartList AS T2
WHERE T1.BCode=T2.BCode AND T2.ULoginName=@pUID
DELETE FROM tShopcartList WHERE ULoginName=@pUID
UPDATE tMemberList
SET PaidPoint=PaidPoint+@pVirtMoney,
VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.05
WHERE LoginName=@pUID
UPDATE tMemberList
SET PaidPoint=PaidPoint+@pVirtMoney,
VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.01
WHERE LoginName=(SELECT RefBy
FROM tMemberList
WHERE LoginName=@pUID)
*****************************************
IF (@@ERROR = 0)
COMMIT TRANSACTION
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
*****************************************
END
GO
-----------------------------------------------------
大概就是这么回事拉,我是实在不行拉。
@pUID varchar(15),
@pPWD varchar(15),
@pOrderName varchar(30),
@pOrderAddress varchar(100),
@pOrderZIP char(6),
@pPhone_p varchar(20),
@pPhone_s varchar(20),
@pEmail varchar(30),
@pVirtMoney money,
@pOrderIP char(15),
@NEWID INT OUTPUT -----**位置放错了AS
--***********************************
--@NEWID INT OUTPUT
--***********************************
IF (NOT EXISTS(
SELECT *
FROM tMemberList
WHERE LoginName=@pUID AND LoginPWD=@pPWD))
BEGIN
RETURN -1
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tOrdersList(LoginName,CustomerName,CustomerAddress,
CustomerZIP,CustomerPhone_p,CustomerPhone_s,
CustomerEmail,VirtMoney,CustomerIP)
VALUES(@pUID,@pOrderName,@pOrderAddress,@pOrderZIP,
@pPhone_p,@pPhone_s,@pEmail,@pVirtMoney,@pOrderIP) INSERT INTO tOrderDetails
SELECT @@IDENTITY,BCode,BQuantity,BMem_Price,BDiscount
FROM vBook_Shopcart
WHERE ULoginName=@pUID
--************************************************* SET @NEWID=@@IDENTITY --你是要得到插入那个表的identity?????--*************************************************
DECLARE @ttlPrice money
SELECT @ttlPrice=(SELECT (SUM(BTotal_Price)-@pVirtMoney) AS ttlPrice
FROM vBook_Shopcart
WHERE ULoginName=@pUID)
UPDATE tBookStocks SET BStocks=T1.BStocks-T2.BQuantity
FROM tBookStocks AS T1,tShopcartList AS T2
WHERE T1.BCode=T2.BCode AND T2.ULoginName=@pUID
DELETE FROM tShopcartList WHERE ULoginName=@pUID
UPDATE tMemberList
SET PaidPoint=PaidPoint+@pVirtMoney,
VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.05
WHERE LoginName=@pUID
UPDATE tMemberList
SET PaidPoint=PaidPoint+@pVirtMoney,
VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.01
WHERE LoginName=(SELECT RefBy
FROM tMemberList
WHERE LoginName=@pUID)
--*****************************************
IF (@@ERROR = 0)
COMMIT TRANSACTION
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
--*****************************************
END
GO
@pUID varchar(15),
@pPWD varchar(15),
@pOrderName varchar(30),
@pOrderAddress varchar(100),
@pOrderZIP char(6),
@pPhone_p varchar(20),
@pPhone_s varchar(20),
@pEmail varchar(30),
@pVirtMoney money,
@pOrderIP char(15)
ASdeclare @NEWID INT -- OUTPUT --加declare,不要outputIF (NOT EXISTS(
SELECT *
FROM tMemberList
WHERE LoginName=@pUID AND LoginPWD=@pPWD))
BEGIN
RETURN -1
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tOrdersList(LoginName,CustomerName,CustomerAddress,
CustomerZIP,CustomerPhone_p,CustomerPhone_s,
CustomerEmail,VirtMoney,CustomerIP)
VALUES(@pUID,@pOrderName,@pOrderAddress,@pOrderZIP,
@pPhone_p,@pPhone_s,@pEmail,@pVirtMoney,@pOrderIP)
INSERT INTO tOrderDetails
SELECT @@IDENTITY,BCode,BQuantity,BMem_Price,BDiscount
FROM vBook_Shopcart
WHERE ULoginName=@pUID
SET @NEWID=@@IDENTITY
DECLARE @ttlPrice money
SELECT @ttlPrice=(SELECT (SUM(BTotal_Price)-@pVirtMoney) AS ttlPrice
FROM vBook_Shopcart
WHERE ULoginName=@pUID)
UPDATE tBookStocks SET BStocks=T1.BStocks-T2.BQuantity
FROM tBookStocks AS T1,tShopcartList AS T2
WHERE T1.BCode=T2.BCode AND T2.ULoginName=@pUID
DELETE FROM tShopcartList WHERE ULoginName=@pUID
UPDATE tMemberList
SET PaidPoint=PaidPoint+@pVirtMoney,
VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.05
WHERE LoginName=@pUID
UPDATE tMemberList
SET PaidPoint=PaidPoint+@pVirtMoney,
VirtMoney=VirtMoney-@pVirtMoney+@pVirtMoney*0.01
WHERE LoginName=(SELECT RefBy
FROM tMemberList
WHERE LoginName=@pUID)
IF (@@ERROR = 0)
BEGIN
COMMIT TRANSACTION
-- SELECT @@IDENTITY
return @NEWID
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
GO