How do I NOT use a t-sql cursor when printing rows is necessary?

Question

A colleague and I wrote this stored proc that documents a database table in wiki markup, for a ScrewTurn wiki system. Originally, I wrote it without a cursor, because until today I never even knew how to use one!

I started with what is essentially a combination of what you see below. I would select one column for each row, where that column was the entire wikimarkup of the row. This worked perfectly, but I wanted to print text before and after the results. I hacked that by using a few unions. I would union the header with the result set and then union all that with the footer. But THEN, I had to insert a row of text between each row, and THAT was the part I could not figure out without using a cursor. In short:

How do I select a bunch of records with a hardcoded row before each result row?

In my case, each row needs to be preceded with a |- row.

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

This is currently working. It prints out exactly none other than the following when run on 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 ||  ||  || 
|}

New code with LittleBobbyTables' answer (it's shorter but involves a lot of string concatenation, and it fails to print when there are more than 8000 characters in the markup):

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

How do I select a bunch of records with hardcoded string before each row?

select '|-I am a hardcoded string with a newline following' 
        + char(10) + a.foo as foo
from bar a;

That is, just concat the hardcoded string onto the column you were already selecting. Separate them with a new line character (char(10)) or, for DOS/Windows, carriage-return linefeed (char(13) + char(10)).

Edit: thanks to everyone who pointed out that the catenation operator is "+", not "||" in T-SQL.

Answer 2

Update: Per Cade Roux and Chris, this doesn't work when printing over 8000 characters. I'm leaving this up as a warning.

You can use a variable to repeatedly add rows to. Try this:

-- 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) 
set @WikiRow = ''

select @WikiRow = @WikiRow + 
        '|- ' + char(10) + '| ' +
        Name + ' || ' + 
        [Type] + ' || ' + 
        Nullable + ' || ' + 
        [Default] + ' || ' + 
        [Identity] + ' || ' + 
        [Description] + char(10) 
    from 
        @WikiDocData 

print left(@WikiRow, len(@WikiRow) - 1)

-- Output footer 
print '|}' 

Answer 3

Here's a routine to print long varchar(max) variables (it requires no distance between CRLF greater than the max threshold for PRINT to work, since it basically takes the string and moves it in a buffer in "lines", and then prints the buffer when it gets over 4000 characters):

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

I personally prefer to use this, since it makes a lot of other code simpler and more versatile without a cursor (for instance code which can go into views or inline table-valued functions is far more reusable).