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?

Improve company productivity with a Business Account.Sign Up

x
 
vastoConnect With a Mentor Commented:
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
 
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.