1.将多个值拼接在同一个字段里。
update Common_Customer
set share_name=(
select stuff( (
select ',' +cs.staff_name
from
view_customer cs
where cs.customer_id=common_customer.customer_id for xml path('')),1,1'') )
where customer_id=@a
2.@@error,用法
@@error是系统函数,当没有发送错误时返回0,如果发生错误时@@error《》0,并返回错误号,每个SL语句执行完,@@error值都会变
BEGIN TRAN Tran_Money --开始事务
DECLARE @tran_error int;
SET @tran_error = 0;
BEGIN TRY
UPDATE tb_Money SET MyMoney = MyMoney - 30 WHERE Name = '刘备';
SET @tran_error = @tran_error + @@ERROR;
--测试出错代码,看看刘备的钱减少,关羽的钱是否会增加
--SET @tran_error = 1;
UPDATE tb_Money SET MyMoney = MyMoney + 30 WHERE Name = '关羽';
SET @tran_error = @tran_error + @@ERROR;
END TRY
BEGIN CATCH
PRINT '出现异常,错误编号:' + convert(varchar,error_number()) + ',错误消息:' + error_message()
SET @tran_error = @tran_error + 1
END CATCH
IF(@tran_error > 0)
BEGIN
--执行出错,回滚事务
ROLLBACK TRAN;
PRINT '转账失败,取消交易!';
END
ELSE
BEGIN
--没有异常,提交事务
COMMIT TRAN;
PRINT '转账成功!';
END
BEGIN TRAN Tran_Money --开始事务
DECLARE @tran_error int;
SET @tran_error = 0;
BEGIN TRY
UPDATE tb_Money SET MyMoney = MyMoney - 30 WHERE Name = '刘备';
SET @tran_error = @tran_error + @@ERROR;
--测试出错代码,看看刘备的钱减少,关羽的钱是否会增加
--SET @tran_error = 1;
UPDATE tb_Money SET MyMoney = MyMoney + 30 WHERE Name = '关羽';
SET @tran_error = @tran_error + @@ERROR;
END TRY
BEGIN CATCH
PRINT '出现异常,错误编号:' + convert(varchar,error_number()) + ',错误消息:' + error_message()
SET @tran_error = @tran_error + 1
END CATCH
IF(@tran_error > 0)
BEGIN
--执行出错,回滚事务
ROLLBACK TRAN;
PRINT '转账失败,取消交易!';
END
ELSE
BEGIN
--没有异常,提交事务
COMMIT TRAN;
PRINT '转账成功!';
END
3.sql错误处理
begin try
select 2/0
end try
begin catch
select error_number() as error_number ,
error_message() as error_message,
error_state() as error_state,
error_severity() as error_severity
end catch
4.sql server批量更新字段
update A
set A.a=B.a
from B
left join C ON B.b=C.c
where