Create dynamic 'Create Table' statement

Hello,
I have this logic below where it gets the columns from a table - 'Proc_Layouts' and there is a while loop that puts these columns together, and at the end, uses these columns to create a table.
I had it working one time, where it did select the columns and create the string - 'col1 varchar(10), col2 varchar(20), col3(varchar2), .....)
But when I added the last statement - 'Create table ' +
It doesn't even print it or retrieve the columns.  Am I out of scope somewhere or is there a better way to write this.
The 'Print @pSQL' statement doesn't seem to work and the result or message page just shows:
'(42 row(s) affected)'
Not showing the DDL statement that is supposed to be shown.
Thanks,
Steve

  Declare @pSQL    varchar(8000)  
, @pTypeKey   int  
, @pEXTColumns    varchar(8000)  
, @FieldOrder   int  
, @ColumnName   varchar(80)  
, @CurrentVariable  Varchar(10)  
, @FieldLength   varchar(4)
,@ud_ext_temp_table varchar(40)

  Set @pTypeKey = 1711
  Set @ud_ext_temp_table = 'My_EXT_Table'
 
 Declare @columns table  
   (  
    FieldOrder int,  
    columnname varchar(80),  
    fieldlength varchar(5)  
   )  
 
   Insert Into @columns (FieldOrder, ColumnName, FieldLength)  
   Select FieldOrder, fieldname, fieldlength  
   From proc_Layouts  
   Where  LayoutKey = @pTypeKey  
 
   Set @FieldOrder = (Select Min(FieldOrder) From @columns)  
 
   While @FieldOrder Is Not Null  
      Begin  
      Select      @ColumnName  = IsNull(columnname,'colxxx'),
            @FieldLength = IsNull(FieldLength, '80')
      From      @columns  
      Where      FieldOrder = @FieldOrder  
             
      Set @pEXTColumns = @pEXTColumns + ', '+@ColumnName+ ' VarChar(' + @FieldLength + ') '  
             
      Set @FieldOrder = (Select Min(fieldorder) From @columns Where fieldorder > @fieldorder)  

      End  
Set @pEXTColumns = right(@pEXTColumns, len(@pEXTColumns)) -- remove the first character (comma) of this string..  

set @pSQL = 'Create Table ' + @ud_ext_temp_table + ' (' + @pEXTColumns + ')'

print @pSQL
MachinegunnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nishant joshiTechnology Development ConsultantCommented:
might you are having an end less while loop try to print in while loop and check.

Regards,
0
Kyle AbrahamsSenior .Net DeveloperCommented:
can you select @pSQL . .. does that return anything?
0
MachinegunnerAuthor Commented:
Thanks for the replies, the @pSQL is coming back as NULL, when I do a 'Select @pSQL' and looks like some kind of endless loop somewhere?
Should I reinitialize @FieldOrder somewhere or another variable, to kick it out of the endless loop?
Thanks,
Steve
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vastoCommented:
Your @pEXTColumns variable has value null and since there is no place where this value is initialized
Set @pEXTColumns = @pEXTColumns + ', '+@ColumnName+ ' VarChar(' + @FieldLength + ') '  
will always set the value to null

add
set @pEXTColumns=''
somewhere at the beginning of the script ( for example after Set @pTypeKey = 1711)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vastoCommented:
also you don't need cursor and temp table . try this code intead:

Declare @pSQL varchar(8000), @pTypeKey int, @ud_ext_temp_table varchar(40)
SELECT @pTypeKey = 1711, @ud_ext_temp_table = 'My_EXT_Table'

SELECT @pSQL = isnull(@pSQL + ',' ,'') + '[' +fieldname + '] VarChar(' + fieldlength  + ') '
FROM proc_Layouts  
WHERE  LayoutKey = @pTypeKey  
ORDER BY fieldorder

SET @pSQL = 'Create Table ' + @ud_ext_temp_table + ' (' + @pSQL + ')'
print @pSQL
0
MachinegunnerAuthor Commented:
Thanks for the solution as the setting of the @pEXTColumns = '' in the beginning, worked and created the DDL statement just fine.
Thanks also for the other logic, for creating without a cursor.
Much appreciated!
Thanks,
Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.