[AccessD] SQL Server - Turn field list into string

Well, I have the first one.  The code creates a cursor and two variables 
- the name of the field and the number "Max Length".  In my case all of 
the fields are varchar so I don't have to deal with that complexity.  
The code then builds up the @SQL one record at a time.  Works a treat as 
our British / Irish friends would say.

The tsql looks like:

ALTER PROCEDURE [dbo].[usp210_CreatetblOrderCriteria]
    -- Add the parameters for the stored procedure here
    @OrderTblName varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
declare @SQL varchar(1000)
declare @ColName varchar(100)
declare @MaxLen varchar(2)


    SELECT @SQL = 'ALTER TABLE [dbo].[' + @OrderTblName + ']
    ADD ['
    Declare FieldCursor CURSOR FAST_FORWARD FOR
    SELECT * FROM avFields_Criteria
   
    open FieldCursor
   
    FETCH NEXT FROM FieldCursor
    INTO @ColName, @MaxLen
   
    WHILE @@FETCH_STATUS = 0
    BEGIN   
        SELECT @SQL = @SQL + @ColName + '] [Char] (' + @MaxLen + ') NULL'
        --print 'ColName: ' + @ColName
        --print 'MaxLen: ' + @MaxLen
        FETCH NEXT FROM FieldCursor
        INTO @ColName, @MaxLen

        IF @@FETCH_STATUS = 0
        BEGIN
            SELECT @SQL = @SQL + ', ['
        END
    END

    CLOSE FieldCursor
    DEALLOCATE FieldCursor

    print @SQL

    begin try
        exec (@SQL)
    end try   
    begin catch
        print 'There was an error ALTERING the table! ' + ERROR_MESSAGE()
    end catch
END

John W. Colby
www.ColbyConsulting.com



Arthur Fuller wrote:
> I just answered this question on the other thread. You can do it using
> COALESCE. I wrote an article on this exact thing for TechRepublic.com a
> while back. I'm hunting for it now.
> Aha! Found it! See http://www.artfulsoftware.com/infotree/tip.php?id=753
>
> hth,
> Arthur
>