Find a single column length in Table and determine if it belongs to the change (stored procedure)

xiaoxiao2021-03-06  99

It is time to judge whether a column belongs to the operational table, compares some annoying to write a stored procedure.

Create Proc Pr_getTableColumnlen (- Incoming Table Name and Column Name @intablename VARCHAR (20), @ INCOLUMNAME VARCHAR (20),

- Introduced marker variables and content variable @LenContent varchar (200), @ flag varchar (1))

AS

- Internal variable declare @ rcolumnname varchar (20), @ rcolumntype var, @ rcolumnnnbyte int, @ rcolumnnnnnnnnete int, @ rcolumnnnnnetestatus varchar (1), @ rtemp varchar (10), @ RTEMPSTR VARCHAR (100)

--Start1 - If there is a judge whether the data is qualified - find the current column, if there is select @ rtemp = len (ColumnProperty (@int (@intablename), @ incolumnname, 'precision')) = (@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. xipe and a.name=@incolumnname - 'h' is Chinese judgment if (Upper (@flag) = 'h') begin if (@lencontent)> @ rcolumnbyte / 2) Begin set @rtempstr = ('Error prompt: column (' @rcolumnname ') type is' @rcolumntepe 'storage (' cast (@ rcolumnbyte / 2 as varchar (10) ) ') Is the Chinese characters' 'can be empty' @rcolumnnnnnnnnelstatus) END ELSE BEGIN SET @RTEMPSTR = 'Y' End Else - Other defaults English begin if (Len (@Lencontent> @rcolumnbyte) Begin Set @RTempstr = ('Error Tips: Column (' @rcolumnname ') type is (' @rcolumntepe ') can be stored (' cast (@

RcolumnByte As Varchar (10)) ') A character' 'can be empty (' @rcolumnnnnnelstatus ')') END ELSE BEGIN SET @RTEMPSTR = 'y' end end endelse begin set @rtempstr = ('(' @incolumnname ') is invalid column') end - start1nd

--Start2, get data with temporary tables CREATE TABLE #TEMTABLE (Result Varchar (100)) Insert Into #TemTable Values ​​(@RTempstr) Select * from #TemTable

--Start2nd

- programmer 2004-08-21 night

转载请注明原文地址:https://www.9cbs.com/read-124769.html

New Post(0)