Matrix conversion operation of implementing data

xiaoxiao2021-03-06  167

/ * --------------------------------- * / / / * function: Matrix conversion operation of data to implement ?? ???? * // * --------------------------------- * / if EXISTS (SELECT NAME FROM sysobjects where xtype = 'p' and name = 'p_jzcaculate') ?? drop procedure p_jzcaculategocreate procedure p_jzcaculate @ tablename varchar (200), @ filter varchar (400) asbegin? set nocount on? declare @col nvarchar (256)? declare @ makesql nvarchar (4000)? declare @insertsql nvarchar (4000)? declare @caculatesql nvarchar (400)? declare @count int? declare @i int? declare @j int? declare @colid int? create table #TempTable (colname nvarchar ( 20))? Select @caculatesql = 'select @ count = count (1) from' @tablename @ filter? EXEC SP_EXECUTESQL @caculatesql, n '@ count int outprut', @ count output? If @count> = 1024? Begin ??? raiserror ('number of rows of the table, the program can't operate', 16, 1)? END? Else? begin ??? SELECT @ i = 0 ??? While @count> 0 ??? begin? ???? SELECT @ i = @ i 1 ????? SELECT @makesql = 'alter table #temptable address cop col' conver (varchar (20), @ i) 'varchar (40)'??? ?? EXEC (@makesql) ????? SELECT @ count = @ count-1 ????? ? End ??? declare my_cursor cursor for ??? select name, colid from syscolumns where id = object_id (@tablename) order by colid ??? open my_cursor ??? fetch next from my_cursor into @ col, @ colid ??? While @@ fetch_status = 0 ??? begin ????? SELECT @ J = Count (1) from syscolumns where id = Object_id (@tablename) ????? ing @colid <@ J-1 ???? Begin ????? SELECT @makesql = 'select @ INSERTSQL = @insertsql case ?????? When Convert (VARCHAR (400),'

@ col ') Is Null Ten' '' 'Else Convert (VARCHAR (400),' @ col ') End ' ',' 'from' @ TableName @ file ????? SELECT @Insertsql = n 'insert #TempTable values ​​(' '' @ col ',' ????? execute sp_executesql @ makesql, N '@ insertsql nvarchar (4000) output', @ insertsql output ????? select @insertsql = replace ( Left (@ insertsql, len (@insertsql) -1), ',', '' ',' ') ' ')' ?????? --select @insertsql ?????? - Print @makesql ????? -? EXEC (@makesql) ????? EXEC (@insertsql) ???? End ??? -? f @@ error <> 0 ??? ? print @@ error ????? fetch next from my_cursor @ col, @ colid ??? End ??? close my_cursor ??? deallocate my_cursor ??? SETNOCOUNT OFF?? DROP TABLE #Temptable? Endendgo

transfer:

Exec p_jzcaculate 'CableInfo_JYDZ', ''

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

New Post(0)