通过多个表,并存储过程
问题描述:
我有两个表中车辆和车辆返回(它在代码拼写错误)搜索,我试图创建一个存储过程,我可以输入发动机号,它会通过搜索车辆及车辆的回报,看它是否对发动机号,它是在任的作品是如果发动机号码不在这里任何表的表,但每一次唯一的条件匹配的是我的代码通过多个表,并存储过程
create procedure outbound
(
@eng varchar(25)
)
AS
BEGIN
BEGIN TRAN
DECLARE @eng_num VARCHAR(25)
DECLARE @eng_num2 VARCHAR(25)
/* SELECT @eng_num= Engine_num from Vehicle where [email protected] and Status=1
SELECT @eng_num2= Engine_num from Vehicle_retuns where [email protected]
IF(@[email protected]_num)
begin
UPDATE Vehicle SET Description_of_Vehicle='Vehicle has ben sent to Manufactory',Status=0 where [email protected]_num
end
ELSE IF(@[email protected]_num2)
begin
UPDATE Vehicle_retuns SET purpose='Vehicle has ben sent to Manufactory',Status=0 where [email protected]_num2
end*/ the lines of code that is the error is occuring
ELSE
SELECT 'No such Engine number was found'
IF(@@ERROR<>0)
BEGIN
SELECT 'An unexpected error has occur'
ROLLBACK TRANSACTION
RETURN -1
END
COMMIT TRANSACTION
END
答
这里是什么,我想可能是这里需要一个粗略的草图。没有必要为单个更新使用显式事务。另外,我强烈建议你看看规范化你的消息,而不是在任何地方对这些长字符串进行硬编码。不确定你是否真的想要根据状态的值设置描述或用途列。我宁愿看到这些是查找值的外键,但也许这在这里不起作用。我还建议你不要使用像“状态”这样的列名。使用保留字是有问题的,原因有很多。最后但并非最不重要的是,您应该使用try/catch块而不是检查@@ ERROR。
- 编辑 - 下面是一些新的代码来处理的事实,你真的需要两个更新。我最初错过了这个。
create procedure outbound
(
@eng varchar(25)
)
AS
BEGIN
IF EXISTS
(
SELECT Engine_num
from Vehicle
where Engine_num = @eng
UNION ALL
SELECT Engine_num
from Vehicle_retuns
where Engine_num = @eng
)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE Vehicle
SET Description_of_Vehicle = 'Vehicle has ben sent to Manufactory'
, Status = 0
where Engine_num = @eng
AND Status = 1
UPDATE Vehicle_retuns
SET purpose = 'Vehicle has ben sent to Manufactory'
, Status = 0
where Engine_num = @eng
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT 'An unexpected error has occurred.'
--I would prefer a message here including the error message and error number instead of just "It failed".
END CATCH
END
ELSE
SELECT 'No such Engine number was found'
END
你尝试过什么? SO不是免费的调试服务。你的帖子基本上是“这是我的代码,为什么它不工作?”。您可能想看看这篇文章。 http://spaghettidba.com/2015/04/24/how-to-post-at-sql-question-on-a-public-forum/ –
但是我说,只有其他工作两个if语句的上方没有关系't works – Deezy
“不起作用”是一个可怕的描述。你是否告诉机械师你的车坏了,或者你的医生说你身体的某个部位不舒服? –