2023年8月3日发(作者：)
sqlserver中⼆进制截断快速判断字段第⼀篇⽂章，把我在做项⽬中遇到的⼆进制截断问题的解决⽅式贡献出来，希望对⼤家有所帮助，废话不多说，下⾯直接贴效果，最后把代码奉上，已经封装为存储过程–创建测试表，简单起见，表结构⾮常简单，但是⼯作中的表字段⾮常多，如果写表的时候报⼆进制截断的错误，是⼀件让⼈很恼⽕的事情，特别是系统上线初期，寻找起来很痛苦，楼主是个懒⼈，出此主意if object_id(‘tempdb…#temp’) is not nulldrop table #tempcreate table #temp(id int identity(1,1) primary key,name char(10))if object_id(‘tempdb…#t’) is not nulldrop table #tselect ‘尼古拉斯凯奇赵四同学’ as name into #tinsert into #temp select * from #t–报错如下–消息 8152，级别 16，状态 14，第 14 ⾏–将截断字符串或⼆进制数据。–语句已终⽌。使⽤存储过程exec GET_tb_col_max ‘#temp’,’#t’,’’,’’输⼊为：此过程⼀共有四个参数，被写⼊表和选择表⽀持同是时实体表和同时是临时表的场景；使⽤过程中的建议和场景1. 写⼊表和选择表同时是实体表，可以⼀次性判断出所有⼆进制截断的字段信息，并且给出修改建议的sql2. 写⼊表和选择表同时是临时表的场景，对⼆进制截断的判断能⼒同第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 ONGOSET QUOTED_IDENTIFIER ONGO--EXEC [GET_tb_col_max] 'spzl_pivas','cwrm','',''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 AS tbname, AS colname,_id, AS col_type_name,
_length,ion,
FROM s a JOIN s b ON _id=_id AND _id=object_id(@tbname)
JOIN c ON _type_id=_type_id
WHERE IN(SELECT name FROM s 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 AS tbname, AS colname,_id, AS col_type_name,
_length,ion,
FROM s a JOIN s b ON _id=_id AND _id=object_id(@tbname)
JOIN c ON _type_id=_type_id
WHERE IN(SELECT name FROM s WHERE object_id=OBJECT_ID(@sel_tbname))
END
--SELECT *FROM s WHERE object_id=object_id('cr_dj_jmjj35') --SELECT *FROM s WHERE object_id=object_id('cr_dj_jmjj35')
--希望追加四个字段，最长度时的值，最⼤长度时的值，最⼤值长度，错误提醒，建议的sql语句 ,遍历插⼊表@sel_tbname
-- SELECT * FROM #tmp RETURN
IF EXISTS(SELECT *FROM sors 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_length_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 'tempdb..' 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 set _min=@col_min, _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,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,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 when _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 set e_error='数据类型不⼀致，请检查' from #tmp a where colname=@colname and col_type_name in('numeric','decimal','float','int','bigint','bit','smallint','byte') and exists(SELECT b.* FROM s b join c on _type_id=_type_id WHERE _id=OBJECT_ID(@sel_tbname) and =@colname and ='varchar' )
end
end
else begin print '先保留'
end
-- -- 找出orders_hld表numeric 类型字段--select , as col_type_name,_col_type from-- s a join b on _type_id=_type_id
--and _id=object_id('orders_hld') and in('numeric','decimal')
--join (select as colname, as in_col_type from s c join d on _type_id=_type_id
--and _id=object_id('jmpz_orders_v_hld') and ='varchar') e on =e
FETCH next FROM cur_cq INTO @colname,@col_type_name,@precision,@scale
END
CLOSE cur_cq
DEALLOCATE cur_cq
update a set _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,另外如果代码有问题，⼤家补充，可以⽀持临时表和实体表的判断 ，转载需要备注来源，谢谢请尊重原创
2023年8月3日发(作者：)
sqlserver中⼆进制截断快速判断字段第⼀篇⽂章，把我在做项⽬中遇到的⼆进制截断问题的解决⽅式贡献出来，希望对⼤家有所帮助，废话不多说，下⾯直接贴效果，最后把代码奉上，已经封装为存储过程–创建测试表，简单起见，表结构⾮常简单，但是⼯作中的表字段⾮常多，如果写表的时候报⼆进制截断的错误，是⼀件让⼈很恼⽕的事情，特别是系统上线初期，寻找起来很痛苦，楼主是个懒⼈，出此主意if object_id(‘tempdb…#temp’) is not nulldrop table #tempcreate table #temp(id int identity(1,1) primary key,name char(10))if object_id(‘tempdb…#t’) is not nulldrop table #tselect ‘尼古拉斯凯奇赵四同学’ as name into #tinsert into #temp select * from #t–报错如下–消息 8152，级别 16，状态 14，第 14 ⾏–将截断字符串或⼆进制数据。–语句已终⽌。使⽤存储过程exec GET_tb_col_max ‘#temp’,’#t’,’’,’’输⼊为：此过程⼀共有四个参数，被写⼊表和选择表⽀持同是时实体表和同时是临时表的场景；使⽤过程中的建议和场景1. 写⼊表和选择表同时是实体表，可以⼀次性判断出所有⼆进制截断的字段信息，并且给出修改建议的sql2. 写⼊表和选择表同时是临时表的场景，对⼆进制截断的判断能⼒同第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 ONGOSET QUOTED_IDENTIFIER ONGO--EXEC [GET_tb_col_max] 'spzl_pivas','cwrm','',''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 AS tbname, AS colname,_id, AS col_type_name,
_length,ion,
FROM s a JOIN s b ON _id=_id AND _id=object_id(@tbname)
JOIN c ON _type_id=_type_id
WHERE IN(SELECT name FROM s 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 AS tbname, AS colname,_id, AS col_type_name,
_length,ion,
FROM s a JOIN s b ON _id=_id AND _id=object_id(@tbname)
JOIN c ON _type_id=_type_id
WHERE IN(SELECT name FROM s WHERE object_id=OBJECT_ID(@sel_tbname))
END
--SELECT *FROM s WHERE object_id=object_id('cr_dj_jmjj35') --SELECT *FROM s WHERE object_id=object_id('cr_dj_jmjj35')
--希望追加四个字段，最长度时的值，最⼤长度时的值，最⼤值长度，错误提醒，建议的sql语句 ,遍历插⼊表@sel_tbname
-- SELECT * FROM #tmp RETURN
IF EXISTS(SELECT *FROM sors 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_length_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 'tempdb..' 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 set _min=@col_min, _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,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,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 when _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 set e_error='数据类型不⼀致，请检查' from #tmp a where colname=@colname and col_type_name in('numeric','decimal','float','int','bigint','bit','smallint','byte') and exists(SELECT b.* FROM s b join c on _type_id=_type_id WHERE _id=OBJECT_ID(@sel_tbname) and =@colname and ='varchar' )
end
end
else begin print '先保留'
end
-- -- 找出orders_hld表numeric 类型字段--select , as col_type_name,_col_type from-- s a join b on _type_id=_type_id
--and _id=object_id('orders_hld') and in('numeric','decimal')
--join (select as colname, as in_col_type from s c join d on _type_id=_type_id
--and _id=object_id('jmpz_orders_v_hld') and ='varchar') e on =e
FETCH next FROM cur_cq INTO @colname,@col_type_name,@precision,@scale
END
CLOSE cur_cq
DEALLOCATE cur_cq
update a set _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,另外如果代码有问题，⼤家补充，可以⽀持临时表和实体表的判断 ，转载需要备注来源，谢谢请尊重原创
发布评论