sql-server - 当需要打印行时，如何不使用 t-sql 游标？

Question

我和一位同事编写了这个存储过程，用于为 ScrewTurn wiki 系统记录 wiki 标记中的数据库表。最初，我写的时候没有光标，因为直到今天我还不知道如何使用光标!

我从本质上是您在下面看到的内容的组合开始。我会为每一行选择一列，该列是该行的整个维基标记。这工作得很好，但我想在结果之前和之后打印文本。我通过使用一些工会来解决这个问题。我将标题与结果集合并，然后将所有内容与页脚合并。但是然后，我必须在每行之间插入一行文本，这是我在不使用光标的情况下无法弄清楚的部分。简而言之:

如何选择每个结果行前都有硬编码行的一组记录？

就我而言，每行前面都需要有一个 |- 行。

set ansi_nulls on
go
set quoted_identifier on
go

alter procedure DocTable
    @TableName varchar(256)
as
begin
    set nocount on;

    declare @WikiDocData table
    (
        Name nvarchar(256),
        [Type] nvarchar(256),
        Nullable nvarchar(256),
        [Default] nvarchar(256),
        [Identity] nvarchar(256),
        [Description] nvarchar(max)
    )

    insert into @WikiDocData
        select
            c.name as Name,
            tp.name + 
                ' (' + 
                (case when c.max_length = -1 then 'MAX' else convert(nvarchar(256),c.max_length) end) +
                ', ' +
                convert(nvarchar(256), c.scale) +
                ', ' +
                convert(nvarchar(256), c.[precision]) + ')'
                as [Type (L,S,P)],
            (case when c.is_nullable = 1 then 'Yes' else '' end) as Nullable,
            isnull(d.[definition], '') as [Default],
            (case when c.is_identity = 1 then 'Yes' else '' end) as [Identity],
            convert(nvarchar(max),isnull(p.value, '')) as [Description]
        from
            sys.tables t 
            inner join sys.columns c on t.object_id = c.object_id
            left join sys.extended_properties p on c.object_id = p.major_id and c.column_id = p.minor_id
            inner join sys.types tp on c.system_type_id = tp.system_type_id
            left join sys.default_constraints d on c.default_object_id = d.object_id and c.column_id = d.parent_column_id
        where
            t.[name] = @TableName 
            and tp.name <> 'sysname'
        order by
            t.object_id,
            c.column_id

    /* Dear reader, if you know how to do this without a cursor, please let me know! */

    -- Output header
    print '{| cellpadding="4" cellspacing="0" border="1"'
    print '! Name !! Type (L,S,P) !! Nullable !! Default !! Identity !! Description'

    -- Output each row and row separator
    declare @WikiRow nvarchar(max)
    declare @GetWikiRow cursor

    set @GetWikiRow = cursor for
        select
            '| ' +
            Name + ' || ' +
            [Type] + ' || ' +
            Nullable + ' || ' +
            [Default] + ' || ' +
            [Identity] + ' || ' +
            [Description]
        from
            @WikiDocData

    open @GetWikiRow fetch next from @GetWikiRow into @WikiRow while @@fetch_status = 0
    begin
        print '|-'
        print @WikiRow
        fetch next from @GetWikiRow into @WikiRow
    end
    close @GetWikiRow
    deallocate @GetWikiRow

    -- Output footer
    print '|}'

end
go

目前该功能正在运行。当在 aspnet_Membership 上运行时，它只会打印出以下内容:

{| cellpadding="4" cellspacing="0" border="1"
! Name !! Type (L,S,P) !! Nullable !! Default !! Identity !! Description
|-
| ApplicationId || uniqueidentifier (16, 0, 0) ||  ||  ||  || 
|-
| UserId || uniqueidentifier (16, 0, 0) ||  ||  ||  || 
|-
| Password || nvarchar (256, 0, 0) ||  ||  ||  || 
|-
| PasswordFormat || int (4, 0, 10) ||  || ((0)) ||  || 
|-
| PasswordSalt || nvarchar (256, 0, 0) ||  ||  ||  || 
|-
| MobilePIN || nvarchar (32, 0, 0) || Yes ||  ||  || 
|-
| Email || nvarchar (512, 0, 0) || Yes ||  ||  || 
|-
| LoweredEmail || nvarchar (512, 0, 0) || Yes ||  ||  || 
|-
| PasswordQuestion || nvarchar (512, 0, 0) || Yes ||  ||  || 
|-
| PasswordAnswer || nvarchar (256, 0, 0) || Yes ||  ||  || 
|-
| IsApproved || bit (1, 0, 1) ||  ||  ||  || 
|-
| IsLockedOut || bit (1, 0, 1) ||  ||  ||  || 
|-
| CreateDate || datetime (8, 3, 23) ||  ||  ||  || 
|-
| LastLoginDate || datetime (8, 3, 23) ||  ||  ||  || 
|-
| LastPasswordChangedDate || datetime (8, 3, 23) ||  ||  ||  || 
|-
| LastLockoutDate || datetime (8, 3, 23) ||  ||  ||  || 
|-
| FailedPasswordAttemptCount || int (4, 0, 10) ||  ||  ||  || 
|-
| FailedPasswordAttemptWindowStart || datetime (8, 3, 23) ||  ||  ||  || 
|-
| FailedPasswordAnswerAttemptCount || int (4, 0, 10) ||  ||  ||  || 
|-
| FailedPasswordAnswerAttemptWindowStart || datetime (8, 3, 23) ||  ||  ||  || 
|-
| Comment || ntext (3000, 0, 0) || Yes ||  ||  || 
|}

LittleBobbyTables 答案的新代码(它更短，但涉及大量字符串连接，并且当标记中超过 8000 个字符时无法打印):

set ansi_nulls on
go
set quoted_identifier on
go

alter procedure DocTable
    @TableName varchar(256)
as
begin
    set nocount on;

    -- Output header 
    print '{| cellpadding="4" cellspacing="0" border="1"' 

    -- Output each row and row separator 
    declare @WikiRow nvarchar(max) 
    set @WikiRow = '! Name !! Type (L,S,P) !! Nullable !! Default !! Identity !! Description'

    select
        @WikiRow = @WikiRow + 
        char(10) + '|- ' + char(10) + '| ' +
        c.name + ' || ' + 
        tp.name + 
            ' (' + 
            (case when c.max_length = -1 then 'MAX' else convert(nvarchar(256),c.max_length) end) +
            ', ' +
            convert(nvarchar(256), c.scale) +
            ', ' +
            convert(nvarchar(256), c.[precision]) + ')' + ' || ' + 
        (case when c.is_nullable = 1 then 'Yes' else '' end) + ' || ' + 
        isnull(d.[definition], '') + ' || ' + 
        (case when c.is_identity = 1 then 'Yes' else '' end) + ' || ' + 
        convert(nvarchar(max),isnull(p.value, ''))
    from
        sys.tables t 
        inner join sys.columns c on t.object_id = c.object_id
        left join sys.extended_properties p on c.object_id = p.major_id and c.column_id = p.minor_id
        inner join sys.types tp on c.system_type_id = tp.system_type_id
        left join sys.default_constraints d on c.default_object_id = d.object_id and c.column_id = d.parent_column_id
    where
        t.[name] = @TableName 
        and tp.name <> 'sysname'
    order by
        t.object_id,
        c.column_id

    print @WikiRow     

    -- Output footer 
    print '|}' 

end
go

Answer 1

这是一个打印 long varchar(max) 变量的例程(它不需要 CRLF 之间的距离大于 PRINT 工作的最大阈值，因为它基本上采用字符串并将其移动到缓冲区中的“行”中，然后当缓冲区超过 4000 个字符时打印缓冲区):

CREATE PROCEDURE [usp_PrintLongSQL]
    @sql varchar(max)
AS
BEGIN
    DECLARE @CRLF AS varchar(2)
    SET @CRLF = CHAR(13) + CHAR(10)

    DECLARE @input AS varchar(max)
    SET @input = @sql

    DECLARE @output AS varchar(max)
    SET @output = ''

    WHILE (@input <> '')
    BEGIN
        DECLARE @line AS varchar(max)
        IF CHARINDEX(@CRLF, @input) > 0
            SET @line = LEFT(@input, CHARINDEX(@CRLF, @input) - 1) + @CRLF
        ELSE
            SET @line = @input

        IF LEN(@input) - LEN(@line) > 0
            SET @input = RIGHT(@input, LEN(@input) - LEN(@line))
        ELSE
            SET @input = ''

        SET @output = @output + @line
        IF LEN(@output) > 4000
        BEGIN
            PRINT @output
            SET @output = ''
        END
    END

    IF @output <> ''
        PRINT @output
END

我个人更喜欢使用它，因为它使许多其他代码在没有光标的情况下变得更简单、更通用(例如，可以进入 View 或内联表值函数的代码的可重用性要高得多)。