T-SQL try catch的使用

最新推荐文章于 2023-02-24 10:08:48 发布
最新推荐文章于 2023-02-24 10:08:48 发布
阅读量261 收藏
点赞数
分类专栏： SQL与数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41617901/article/details/118154576
版权

SQL 数据库 表操作 数据插入 数据库初始化
关键词由CSDN通过智能技术生成 

IF EXISTS (
	SELECT
		*
	FROM
		sys.databases
	WHERE
		name = 'DBTEST'
)
BEGIN
	PRINT 'DBTEST数据库存在' ;
IF object_id(N'PEOPLE', N'U') IS NOT NULL
BEGIN
	PRINT '表PEOPLE存在' ; --向员工表添加数据
   BEGIN  try
	INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		1,
		3,
		'刘备',
		'男',
		'1984-7-9',
		20000,
		'13554785452',
		'成都',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		1,
		2,
		'孙尚香',
		'女',
		'1987-7-9',
		15000,
		'13256854578',
		'荆州',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		1,
		1,
		'关羽',
		'男',
		'1988-8-8',
		12000,
		'13985745871',
		'荆州',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		2,
		1,
		'张飞',
		'男',
		'1990-8-8',
		8000,
		'13535987412',
		'宜昌',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		2,
		3,
		'赵云',
		'男',
		'1989-4-8',
		9000,
		'13845789568',
		'宜昌',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		3,
		3,
		'马超',
		'男',
		'1995-4-8',
		9500,
		'13878562568',
		'香港',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		3,
		2,
		'黄盖',
		'男',
		'1989-4-20',
		8500,
		'13335457412',
		'武汉',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		3,
		1,
		'貂蝉',
		'女',
		'1989-4-20',
		6500,
		'13437100050',
		'武汉',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		2,
		2,
		'曹操',
		'男',
		'1987-12-20',
		25000,
		'13889562354',
		'北京',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		2,
		3,
		'许褚',
		'男',
		'1981-11-11',
		9000,
		'13385299632',
		'北京',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		2,
		1,
		'典韦',
		'男',
		'1978-1-13',
		8000,
		'13478545263',
		'上海',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		1,
		1,
		'曹仁',
		'男',
		'1998-12-12',
		7500,
		'13878523695',
		'深圳',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		1,
		3,
		'孙坚',
		'男',
		'1968-11-22',
		9000,
		'13698545841',
		'广州',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		3,
		3,
		'孙策',
		'男',
		'1988-1-22',
		11000,
		'13558745874',
		'深圳',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		3,
		2,
		'孙权',
		'男',
		'1990-2-21',
		12000,
		'13698745214',
		'深圳',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		3,
		2,
		'大乔',
		'女',
		'1995-2-21',
		13000,
		'13985478512',
		'上海',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		2,
		1,
		'小乔',
		'女',
		'1996-2-21',
		13500,
		'13778787874',
		'北京',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		1,
		2,
		'周瑜',
		'男',
		'1992-10-11',
		8000,
		'13987455214',
		'武汉',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		2,
		3,
		'鲁肃',
		'男',
		'1984-9-10',
		5500,
		'13254785965',
		'成都',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		3,
		3,
		'吕蒙',
		'男',
		'1987-5-19',
		8500,
		'13352197364',
		'成都',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		1,
		1,
		'陆逊',
		'男',
		'1996-5-19',
		7500,
		'13025457392',
		'南京',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		1,
		2,
		'太史慈',
		'男',
		'1983-6-1',
		7500,
		'13077778888',
		'上海',
		getdate()
	) INSERT INTO People (
		DepartmentId,
		RankId,
		PeopleName,
		PeopleSex,
		PeopleBirth,
		PeopleSalary,
		PeoplePhone,
		PeopleAddress,
		PeopleAddTime
	)
VALUES
	(
		1,
		2,
		'aaaa',
		'男',
		'1983-6-1',
		7500,
		'13077778888',
		'上海',
		getdate()
	)
  end try
  BEGIN catch
       insert  into ExceptionLog  VALUES(CONCAT(ERROR_MESSAGE(),ERROR_LINE(),ERROR_NUMBER()),'FUNCid');
  end catch

END
ELSE

BEGIN
	PRINT '表PEOPLE不存在' ; CREATE TABLE People (
		PeopleId INT PRIMARY KEY IDENTITY (1, 1),
		DepartmentId INT REFERENCES Department (DepartmentId) NOT NULL,
		RankId INT REFERENCES [Rank] (RankId) NOT NULL,
		PeopleName nvarchar (50) NOT NULL,
		PeopleSex nvarchar (1) DEFAULT ('男') CHECK (
			PeopleSex = '男'
			OR PeopleSex = '女'
		) NOT NULL,
		PeopleBirth datetime NOT NULL,
		PeopleSalary DECIMAL (12, 2) CHECK (
			PeopleSalary >= 1000
			AND PeopleSalary <= 100000
		) NOT NULL,
		PeoplePhone nvarchar (20) UNIQUE NOT NULL,
		PeopleAddress nvarchar (100),
		PeopleAddTime smalldatetime DEFAULT (getdate())
	)
END
END
ELSE

BEGIN
	PRINT 'DBTEST数据库不存在' ;
END

碧山云游
关注
专栏目录
Inside Microsoft SQL Server 2008 T-SQL Programming
03-02
9. **错误处理和日志记录**：学习如何在T-SQL中处理运行时错误，使用TRY-CATCH结构进行异常处理，以及日志记录的方法。 10. **性能优化**：探讨查询优化器的工作原理，如何使用索引提高查询性能，以及分析查询执行...
【SQL server速成之路】T-SQL语言（二）
m0_61684063的博客
04-26 1734
T-SQL语言运算符与表达式流程控制语句BEGIN…END语句块IF...ELSE语句GOTO语句WHILE、BREAK和CONTINUE语句RETURN语句WAITFOR语句TRY…CATCH语句 运算符与表达式 1．算术.
T-SQL编程中的异常处理-异常捕获(try catch)与抛出异常(throw)
weixin_34019144的博客
04-21 926
  本文出处： http://www.cnblogs.com/wy123/p/6743515.html     T-SQL编程与应用程序一样，都有异常处理机制，比如异常的捕获与异常的抛出(try catch throw)，本文简单介绍异常捕获与异常抛出在T-SQL编程中的实际使用 。 异常处理简单说明 异常捕获在应用程序编程中非常常见，提供了处理程序运行时出现的任何意外或异常情况的方法刚...
T-SQL存储过程中try和catch以及错误处理的用法例子
苦寒的专栏
12-13 1980
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= ALTER PRO
SQL——try catch 定义解析 处理sql错误处理
BuGuoBing
03-14 2872
Try…Catch的定义 　　为了理解Try…Catch块，你必须先明白异常处理的概念。异常处理非常简单：在执行你的代码时，那些非计划之内发生的事情就是异常，你需要做一些事情对它进行处理。至于如何处理异常，则由开发人员，也就是你来决定。 　为了是你的T-SQL代码可以处理异常，你就需要使用Try…Catch块。如果你使用过Visual Studio，那么你可能对Try…Catch块
t-sql基础教程
08-05
10. **错误处理**：使用`TRY...CATCH`结构可以捕获和处理运行时错误，提高程序的健壮性。 本教程可能涵盖了以上所有内容，并结合SQL Server 2008和2005的实际应用，提供实例解析和代码示例。"jb51.net.txt"可能是...
T-SQL示例大全.rar_sql_t-sql_windows sql
09-21
12. **错误处理**：使用`TRY...CATCH`结构来捕获和处理运行时错误，增强程序的健壮性。 13. **窗口函数**：如`RANK()`, `ROW_NUMBER()`, `LEAD()`, `LAG()`等，用于在结果集中进行行级计算和排名。 14. **JOIN操作...
一些 T-SQL 技巧
09-11
使用TRY...CATCH结构可以捕获并处理运行时错误，确保程序的健壮性。 掌握这些T-SQL技巧，可以显著提高你在SQL Server环境中的工作效率，更好地管理和维护数据库。同时，不断学习和探索新的特性和最佳实践，将使你...
SQL Server 2005 中使用 Try Catch 处理异常
09-10
在SQL Server 2005中，异常处理机制得到了显著改进，引入了TRY...CATCH结构，这使得处理错误的方式更加类似于高级编程语言如C#和Java。在SQL Server 2000中，异常处理主要依赖于@@ERROR变量，但这种方式存在局限性，...
SQL SERVER 里的错误处理（try catch)
09-11
SQL SERVER里，也有TRY CATCH。格式如下
T-SQL 异常处理
cuiju3758的博客
05-12 175
1. try/catchSET XACT_ABORT off BEGIN TRYBEGIN TRAN--do sth hereCOMMIT TRANPRINT 'Transaction committed'END TRYBEGI...
T-SQL 中获取错误信息
11-23 2462
在 Transact-SQL 中两种获取错误信息的方式 （1）在 TRY...CATCH 构造的 CATCH 块的作用域内，使用以下系统函数：         ERROR_LINE()，返回出现错误的行号。         ERROR_MESSAGE()，返回将返回给应用程序的消息文本。该文本包括为所有可替换参数提供的值，如长度、对象名或时间。
sql 的 try catch使用
进来看一眼嘛
08-23 3624
记录备忘，以前基本还没用到过sql 的try catch；但是在数据清理某些数据，使用到触发器和存储过程的时候，try catch就必须被使用了； 例如： 当try内容无error时候，try内容返回真实值； 当try内容有error时候，catch内容可获取异常值； 【这种方式好像只能获取到严重级别20以下的error】 基本用法得这样使用： declare @errors as int SET @errors=0 begin try select 3/2 end try begin catch s
SQL Server中的TRY CATCH捕捉异常
gly1653810310的博客
03-25 4469
在SQL Server中用TRY CATCH捕捉异常
SQL之事务、回滚和TRY
最新发布
yuanxi21的博客
02-24 619
事务
Sql语法高级应用之六：如何在Sql语句中如何使用TRY...CATCH
weixin_30402085的博客
07-11 371
TRY...CATCH使用范例 BEGIN TRY //逻辑语句块 END TRYBEGIN CATCH //Catch异常处理块 SET @msg = ERROR_MESSAGE(); PRINT @msg; END CATCH; 使用 TRY...CATCH 构造时，请遵循下列规则和建议： 每个 TRY...CATCH 构...
t-sql - try catch的使用简单例子
icewizardry的专栏
12-11 806
ERROR_NUMBER() – 返回错误号。ERROR_SEVERITY() – 返回错误严重级别。ERROR_STATE() – 返回错误状态。ERROR_PROCEDURE() - 返回错误所在的存储过程或触发器的名称。ERROR_LINE() - 返回错误所在行的行号。ERROR_MESSAGE() - 返回错误的实际信息。begin trydeclare @msg datetime;se
sql etl_使用SQL TRY函数进行ETL优化
culuo4781的博客
07-18 1028
sql etl 介绍 (Introduction) An enterprise data warehouse ETL solution typically includes, amongst other steps, a data transformation step that converts source data from one data type into another. I...
PL/SQL与T-SQL对比详解
- T-SQL：使用TRY...CATCH结构处理异常。 7. **类型属性** - PL/SQL：有%TYPE和%ROWTYPE，用于创建与已有表列相同数据类型的变量。 - T-SQL：没有直接对应的特性，但可以使用系统函数如`SELECT @var = column_...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值