sqlserver中⼆进制截断快速判断字段
第⼀篇⽂章，把我在做项⽬中遇到的⼆进制截断问题的解决⽅式贡献出来，希望对⼤家有所帮助，废话不多说，下⾯直接贴效果，最后把代码奉上，已经封装为存储过程
–创建测试表，简单起见，表结构⾮常简单，但是⼯作中的表字段⾮常多，如果写表的时候报⼆进制截断的错误，是⼀件让⼈很恼⽕的事情，特别是系统上线初期，寻起来很痛苦，楼主是个懒⼈，出此主意
if object_id(‘tempdb…#temp’) is not null
drop table #temp
create table #temp
(id int identity(1,1) primary key,
name char(10)
)
if object_id(‘tempdb…#t’) is not null
drop table #t
select ‘尼古拉斯凯奇赵四同学’ as name into #t
insert into #temp select * from #t
–报错如下
–消息 8152，级别 16，状态 14，第 14 ⾏
–将截断字符串或⼆进制数据。
–语句已终⽌。
使⽤存储过程
exec GET_tb_col_max ‘#temp’,’#t’,’’,’’
输⼊为：
此过程⼀共有四个参数，被写⼊表和选择表⽀持同是时实体表和同时是临时表的场景；
使⽤过程中的建议和场景
1. 写⼊表和选择表同时是实体表，可以⼀次性判断出所有⼆进制截断的字段信息，并且给出修改建议的sql
2. 写⼊表和选择表同时是临时表的场景，对⼆进制截断的判断能⼒同第1场景
3. 如果是临时表和实体表同时存在，建议通过select * into 实体表名称 from 临时表，这种⽅式将临时表转换到实体表，然后采⽤第1
种场景判断
4. 如何该过程有不够强⼤的地⽅还请⼤家通过qq联系我，我尽⼒完善
5. 临时表名称不能使⽤#tmp 因为判断过程中使⽤到了这个临时表名称
6. 另外⼤家经常遇到varchar或者其它字符型数据转换到某个值类型出错的情况，下篇⽂章我来说下如何快速定位这样的字段，解决⼤家
实施过程中的这样问题的烦恼
ALTER PROCEDURE [dbo].[GET_tb_col_max]
@tbname varchar(200), --被写⼊表要求⽀持临时表检索
@sel_tbname varchar(200)=’’,–选择列表可以为空，为空则查询表定义
@in_tb_col_type varchar(30)=’’,–1、插⼊表特定类型字段检索异常，D代表数值类型，没有明确int，decimal,float,numeric，等数值类型
–2、C：代表字符类型
–3、⾮C且⾮D，则检索指定特定数据类型
–4、如果为空字符 ‘’ 则代表不查特定异常
@sel_tb_col_type varchar(30)=’’–此字段含义同上
USE [CR_JMPZZX_V4_BEW]
GO
/****** Object:  StoredProcedure [dbo].[GET_tb_col_max]    Script Date: 2019-04-22 13:57:28 ******/
/****** Object:  StoredProcedure [dbo].[GET_tb_col_max]    Script Date: 2019-04-22 13:57:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC [GET_tb_col_max] 'spzl_pivas','cwrm','',''
decimal是整数数据类型ALTER  PROCEDURE [dbo].[GET_tb_col_max]
@tbname varchar(200),  --被写⼊表要求⽀持临时表检索
@sel_tbname varchar(200)='',--选择列表可以为空，为空则查询表定义
@in_tb_col_type varchar(30)='',--1、插⼊表特定类型字段检索异常，D代表数值类型，没有明确int，decimal,float,numeric，等数值类型                              --2、C：代表字符类型
--3、⾮C且⾮D，则检索指定特定数据类型
--4、如果为空字符 '' 则代表不查特定异常
@sel_tb_col_type varchar(30)=''--此字段含义同上
AS BEGIN
--set @tbname='orders_cq'
--获取被写⼊表的列定义
--SELECT dj_sn FROM tt
DECLARE @col_length_max INT ,@col_max NVARCHAR(max),@col_min NVARCHAR(MAX),@sql_statement nvarchar(max)
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE #tmp
(tbname VARCHAR(200),
colname VARCHAR(200),
column_id INT ,
col_type_name VARCHAR(200),
max_length INT ,
col_max NVARCHAR(MAX),
col_min NVARCHAR(max),
col_length_max INT,
precision INT,
scale INT,
message_error VARCHAR(200),
sql_statement VARCHAR(500)
)
--如果@tbname和@sel_tbname 都是临时表
IF CHARINDEX('#',@tbname)=1 AND CHARINDEX('#',@sel_tbname)=1
BEGIN
PRINT '全部都是临时表'
select @tbname='[tempdb]..'+@tbname,@sel_tbname='[tempdb]..'+@sel_tbname
print @tbname
print @sel_tbname
INSERT INTO #tmp(tbname,colname,column_id,col_type_name,max_length,precision,scale)
SELECT a.name AS tbname,b.name AS lumn_id,c.name AS col_type_name,
b.max_length,b.precision,b.scale
FROM tempdb.sys.objects a JOIN lumns b ON a.object_id=b.object_id AND b.object_id=object_id(@tbname)
JOIN pes c ON b.user_type_id=c.user_type_id
WHERE b.name IN(SELECT name FROM lumns WHERE object_id=OBJECT_ID(@sel_tbname))
print 'iiii'
END
ELSE
BEGIN
PRINT '不全部都是临时表'
INSERT INTO #tmp(tbname,colname,column_id,col_type_name,max_length,precision,scale)
SELECT a.name AS tbname,b.name AS lumn_id,c.name AS col_type_name,
b.max_length,b.precision,b.scale
FROM sys.objects a lumns b ON a.object_id=b.object_id AND b.object_id=object_id(@tbname)
pes c ON b.user_type_id=c.user_type_id
WHERE b.name IN(SELECT name lumns WHERE object_id=OBJECT_ID(@sel_tbname))
END
--SELECT *lumns WHERE object_id=object_id('cr_dj_jmjj35')
--SELECT *lumns WHERE object_id=object_id('cr_dj_jmjj35')
--希望追加四个字段，最长度时的值，最⼤长度时的值，最⼤值长度，错误提醒，建议的sql语句 ，遍历插⼊表@sel_tbname
-
- SELECT * FROM #tmp RETURN
IF EXISTS(SELECT *FROM sys.syscursors WHERE cursor_name='cur_cq')
BEGIN
CLOSE cur_cq
DEALLOCATE cur_cq
END
DECLARE @colname VARCHAR(200),@sql NVARCHAR(2000),@col_type_name VARCHAR(200),@precision INT,@scale INT
DECLARE cur_cq CURSOR FOR SELECT colname,col_type_name,precision,scale FROM #tmp
OPEN cur_cq
FETCH next FROM cur_cq INTO @colname,@col_type_name,@precision,@scale
WHILE @@FETCH_STATUS=0
BEGIN
if @in_tb_col_type='' and @sel_tb_col_type=''
begin
--1、获取表@sel_tbname 中的列@colname 最⼤值，最⼤值长度
SET @sql=N'select @col_min=min('+RTRIM(QUOTENAME(@colname))+') ,@col_max=max('+RTRIM(QUOTENAME(@colname))+'),@col_len gth_max=max(datalength(rtrim('+QUOTENAME(@colname)+'))) from '+CASE WHEN CHARINDEX('#',@sel_tbname)=1 THEN 'tempdb..' ELSE '' END + @ sel_tbname
PRINT @sql
EXEC sp_executesql @sql,N'@col_min nvarchar(max) output,@col_max nvarchar(max) output,@col_length_max int output',
@col_max =@col_max OUTPUT,@col_length_max=@col_length_max OUTPUT,@col_min=@col_min output
--output 关键字前⾯的变量是输出变量，⼀定要在代码中声明
--如果是字符类型数据，则最⼤值重新定义为，最⼤字节长度时的值
IF @col_type_name IN ('varchar','char','nchar')
BEGIN
--sp_exectutesql 同时存在输⼊参数和输出参数
SET @sql='SELECT @col_max='+RTRIM(QUOTENAME(@colname))+ ' FROM '+CASE WHEN CHARINDEX('#',@sel_tbname)=1 THEN 'te mpdb..' ELSE '' END + @sel_tbname +' WHERE DATALENGTH('+RTRIM(QUOTENAME(@colname))+')=@col_length_max'
PRINT @sql
EXEC sp_executesql @sql,N'@col_max nvarchar(max) output,@col_length_max int',@col_max =@col_max OUTPUT,@col_length_max = @col_length_max
END
-
-更新#tmp 表字段col_max,col_length_max
print '@col_type_name ='+cast(@col_type_name as varchar(max))
print '@precision ='+cast(@precision as varchar(max))
print '@scale ='+cast(@scale as varchar(max))
PRINT '@col_min ='+cast(@col_min as varchar(max))
PRINT '@col_max ='+cast(@col_max as varchar(max))
PRINT '@col_length_max ='+cast(@col_length_max as varchar(max))
--if @col_type_name='decimal' and @col_max<=0.0
--continue
SET @sql=N'update a l_min=@col_min, a.col_max=@col_max , col_length_max=@col_length_max,
message_error=case
when col_type_name=''int'' and @col_max>power(cast(2 as bigint),31) then ''整数值超过最⼤值2147483648''
when col_type_name=''int'' and @col_min<-power(cast(2 as bigint),31)+1 then ''整数值最⼩值为-2147483647，请检查''
when  col_type_name=''decimal'' and CAST(SUBSTRING(@col_max,1,CHARINDEX(''.'',@col_max)-1) AS bigint)>CAST(REPLICATE(9,precision-scale) AS bigint)
then ''decimal数据类型，整数部分溢出''
when  col_type_name=''decimal'' and CAST(SUBSTRING(@col_max,1,CHARINDEX(''.'',@col_max)-1) AS bigint)>CAST(REPLICATE(9,precision-scale) AS bigint)
then ''decimal数据类型，整数部分溢出''
when  col_type_name=''numeric'' and CAST(SUBSTRING(@col_max,1,CHARINDEX(''.'',@col_max)-1) AS bigint)>CAST(REPLICATE(9,pr
ecision-scale ) AS bigint)
then ''decimal数据类型，整数部分溢出''
when  col_type_name=''numeric'' and CAST(SUBSTRING(@col_max,1,CHARINDEX(''.'',@col_max)-1) AS bigint)>CAST(REPLICATE(9,precision-scale ) AS bigint)
then ''numeric数据类型，整数部分溢出''
when col_type_name not in(''int'',''decimal'',''numeric'',''datetime'') and @col_length_max>max_length then ''字符超出''
else '''' end
FROM #tmp a where 1= case l_type_name in(''decimal'',''numeric'') and  CHARINDEX(''.'',@col_max)=0 then 0 else 1 end
and colname=' +''''+@colname+''''
PRINT @sql
EXEC sp_executesql @sql,N'@col_max nvarchar(max),@col_length_max int,@col_min nvarchar(max)',
@col_max=@col_max,@col_length_max=@col_length_max,@col_min=@col_min
@col_max=@col_max,@col_length_max=@col_length_max,@col_min=@col_min
end
else if @in_tb_col_type<>''
begin
--如果插⼊表，字段类型不为空
--场景1：插⼊表字段是数值型，但是不知道准确的数据类型，选择列表的字段类型确定，⽐如说，选择列表字段数据类型varchar
if @in_tb_col_type='D' and @sel_tb_col_type='varchar'
begin
update a ssage_error='数据类型不⼀致，请检查' from #tmp a where colname=@colname and col_type_name
in('numeric','decimal','float','int','bigint','bit','smallint','byte')
and  exists(SELECT b.* lumns b pes c on b.user_type_id=c.user_type_id
WHERE b.object_id=OBJECT_ID(@sel_tbname) and b.name=@colname and c.name='varchar' )
end
end
else
begin
print '先保留'
end
--      -- 出orders_hld表numeric 类型字段
--select a.name,b.name as col_type_name,e.in_col_type from
-- lumns a pes b on a.user_type_id=b.user_type_id
--and a.object_id=object_id('orders_hld') and b.name in('numeric','decimal')
--join (select c.name as colname,d.name as in_col_type lumns c pes d on c.user_type_id=d.user_type_id
--and c.object_id=object_id('jmpz_orders_v_hld') and d.name='varchar') e on a.lname
FETCH next FROM cur_cq INTO @colname,@col_type_name,@precision,@scale
END
CLOSE cur_cq
DEALLOCATE cur_cq
update a set a.sql_statement='alter table '+tbname+' alter column '+colname+' '+col_type_name+'('+cast(col_length_max*2 as varchar(200))+')' from
#tmp  a where message_error='字符超出'
SELECT *FROM #tmp WHERE message_error<>''
END
本⽂纯原创，如果疑问可以联系我qq :2625526306，另外如果代码有问题，⼤家补充，可以⽀持临时表和实体表的判断 ，转载需要备注来源，谢谢请尊重原创

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。