将Text字段的每一行转换为临时表的记录的存储过程

good_liao

/*!*创建将text/ntext型字段的每一行转换为记录的存储过程pr_LineToRec*!*/
/*!*功能：读取Text字段的每一行，并将其插入到临时表中          *!*/
/*!*参数：@cTableName Varchar(116)：包含text字段的表名；      *!*/
/*!*      @cFieldName Varchar(100)：要读取的text字段名称；    *!*/
/*!*      @cWhere Varchar(8000)：要读取的text字段的条件表达式 *!*/
/*!*使用方法：EXECUTE dbo.pr_LineToRec @cTableName = 'dbo.myTab1', @cFieldName = 'cFields1', @cWhere = 'nNo = 1' *!*/
/*!*返回：返回包含cFields1内容的结果集，cFields1中的每一行对应结果集的c_Class的一条记录 *!*/
IF OBJECT_ID('dbo.pr_LineToRec') IS NOT NULL
  DROP PROCEDURE dbo.pr_LineToRec
GO
CREATE PROCEDURE dbo.pr_LineToRec
    @cTableName Varchar(116) = 'dbo.myTab1',
    @cFieldName Varchar(100) = 'cFields1',
    @cWhere Varchar(8000) = 'nNo = 1'
    WITH ENCRYPTION
AS
BEGIN
    IF ISNULL(@cTableName, '') = ''
        SELECT @cTableName = 'dbo.myTab1'
    IF ISNULL(@cFieldName, '') = ''
        SELECT @cFieldName = 'cFields1'
    IF ISNULL(@cWhere, '') = ''
        SELECT @cWhere = 'nNo = 1'
    EXECUTE('SELECT ' + @cFieldName + ' AS m_myField INTO ##myCursor FROM ' + @cTableName + ' WHERE ' + @cWhere)
    DECLARE @cClassify VarChar(8000), @cSubStr Varchar(100), @nAt Integer
    SELECT @cClassify = m_myField FROM ##myCursor
    SELECT @cClassify = ISNULL(@cClassify, '')
    DROP TABLE ##myCursor
    CREATE TABLE #myClassify (c_Class Varchar(100))
    SELECT @nAt = CHARINDEX (CHAR(10), @cClassify), @cSubStr = NULL
    WHILE @nAt > 0
    BEGIN
        SELECT @cSubStr = LEFT(@cClassify, @nAt), @cClassify = SUBSTRING(@cClassify, @nAt, 8000)
        SELECT @cSubStr = REPLACE(@cSubStr, CHAR(10), '')
        SELECT @cSubStr = REPLACE(@cSubStr, CHAR(13), '')
        IF LEFT(@cClassify, 1) = CHAR(10)
            SELECT @cClassify = SUBSTRING(@cClassify, 2, 8000)
        IF LEFT(@cClassify, 1) = CHAR(13)
            SELECT @cClassify = SUBSTRING(@cClassify, 2, 8000)
        IF @cSubStr <> ''
            INSERT INTO #myClassify (c_Class) VALUES (@cSubStr)
        SELECT @nAt = CHARINDEX(CHAR(10), @cClassify), @cSubStr = NULL
    END
    SELECT @cClassify = REPLACE(@cClassify, CHAR(10), '')
    SELECT @cClassify = LTRIM(RTRIM(REPLACE(@cClassify, CHAR(13), '')))
    IF @cClassify <> ''
        INSERT INTO #myClassify (c_Class) VALUES (@cClassify)
    SELECT * FROM #myClassify
END
GO