- 作者:xiaoxiao
- 发表时间:2020-12-23 11:01
- 来源:未知
做程序有时候,要判断某列是否属于操作表,对比有些烦就写了一个存储过程。
create proc Pr_getTableColumnLen(--传入的表名和列名@inTableName varchar(20),@inColumnName varchar(20),
--传入的标志变量和内容变量@lenContent varchar(200),@flag varchar(1))
as
--内部变量declare@rColumnName varchar(20),@rColumnType varchar(10),@rColumnByte int,@rColumnNullStatus varchar(1),@rTemp varchar(10),@rTempStr varchar(100)
--start1--如果存在判断存入数据是否合格--查找当前列,是否存在SELECT @rTemp=len(COLUMNPROPERTY( OBJECT_ID(@inTableName),@inColumnName,'PRECISION'))if(@rTemp > 0) begin select @rColumnName=a.name from syscolumns a,systypes b where a.id =object_id(@inTableName) and a.xtype = b.xtype and a.name=@inColumnName select @rColumnNullStatus=case a.isnullable when 1 then 'Y' else 'N' end from syscolumns a,systypes b where a.id =object_id('publicnotice') and a.xtype = b.xtype and a.name=@inColumnName select @rColumnType=b.name from syscolumns a,systypes b where a.id =object_id(@inTableName) and a.xtype = b.xtype and a.name=@inColumnName select @rColumnByte=a.length from syscolumns a,systypes b where a.id =object_id(@inTableName) and a.xtype = b.xtype and a.name=@inColumnName --‘H’是中文判断 if(upper(@flag) ='H') begin if(len(@lenContent) > @rColumnByte/2) begin set @rTempStr = ('出错提示:列('+ @rColumnName + ') 类型是 ' + @rColumnType + ' 可存储(' + cast(@rColumnByte/2 as varchar(10)) + ')个汉字' + ' 可否为空 ' + @rColumnNullStatus) end else begin set @rTempStr = 'Y' end end else --其他默认为英文 begin if(len(@lenContent) > @rColumnByte) begin set @rTempStr = ('出错提示:列('+ @rColumnName + ') 类型是(' + @rColumnType + ') 可存储(' + cast(@rColumnByte as varchar(10)) + ')个字符' + ' 可否为空(' + @rColumnNullStatus + ')') end else begin set @rTempStr = 'Y' end end endelse begin set @rTempStr =( '(' + @inColumnName + ') 是无效列') end
--start1End
--start2,用临时表得到数据create table #temTable (result varchar(100))insert into #temTable values(@rTempStr)select * from #temTable