Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Lightbulb Unanswered: please help me to correct its infinity loop(Dynamic Create Table in Stored Procedure)

    I wrote it finally , but it has an unwanted infinity loop , please help me to correct its infinity loop :
    Code:
    CREATE PROCEDURE [dbo].[CreateMyTable] ( @FieldsNameStr varchar(2000),
              @FieldsTypeStr varchar(2000),
             @TableName varchar(2000) )
    
    
    AS
    declare  @i int
    declare  @j int
    declare  @TempStr varchar(200)
    declare  @TempStr1 varchar(200)
    declare  @TempStr2 varchar(200)
    
    
    
    set @i = 0
    set @j = 0
    set @TempStr = ''
    
    WHILE (@i < Len(@FieldsNameStr)) or (@j < Len(@FieldsTypeStr))
    begin
    
      set @TempStr1 = ''
      while  not(substring(@FieldsNameStr, @i, 1) = ',') or 
        (@i < Len(@FieldsNameStr))
      begin
        set @TempStr1 = @TempStr1 + substring(@FieldsNameStr, @i, 1)
        set @i = @i +1
      end
    
    
      set @TempStr2 = ''
      while  not(substring(@FieldsTypeStr, @j, 1) = ',') or 
        (@i < Len(@FieldsTypeStr))
      begin
        set @TempStr2 = @TempStr2 + substring(@FieldsTypeStr, @j, 1)
        set @j = @j +1
      end
    
      set @TempStr = @TempStr + '[' +@TempStr1+ ']' + @TempStr2 +' NOT NULL ,'
      set @i = @i +1
      set @j = @j +1
    end
    
    
    EXEC('CREATE TABLE [dbo].[' + @TableName + '] ( '+
              @TempStr +	
             ') ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]')
    GO

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    It's looks like T-SQL (SQL Server), and it looks like you are looking for the first occurrence of a ',' in your strings.
    If both are the case, have a look at the CHARINDEX function. It can give you the exact location of the comma, so you can get rid of some of the loops.

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Oh, and looking at your code a bit closer:
    while not(substring(@FieldsTypeStr, @j, 1) = ',') or
    (@i < Len(@FieldsTypeStr))
    That @i most likely needs to be @j.

  4. #4
    Join Date
    Nov 2005
    Posts
    5
    Quote Originally Posted by ivon
    It's looks like T-SQL (SQL Server), and it looks like you are looking for the first occurrence of a ',' in your strings.
    If both are the case, have a look at the CHARINDEX function. It can give you the exact location of the comma, so you can get rid of some of the loops.
    I checkede it on Books Online and found CHARINDEX used in column strings not in strings! Am I right?

  5. #5
    Join Date
    Nov 2005
    Posts
    5
    Quote Originally Posted by ivon
    Oh, and looking at your code a bit closer:
    while not(substring(@FieldsTypeStr, @j, 1) = ',') or
    (@i < Len(@FieldsTypeStr))
    That @i most likely needs to be @j.
    Thanks , as you said I changed it but it didn't make any diffrence.

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    CHARINDEX does work on varchar variables:
    Code:
    declare @i int
    declare @a varchar(1)
    declare @b varchar(6)
    
    set @a = 'B'
    set @b = 'ABCDEF'
    
    set @i = CHARINDEX(@a,@b)
    select @i
    works for me.

    Also, I notice that your @TempStr will end in a comma. So even if we manage to solve the infinity thing, the EXEC command will fail.

  7. #7
    Join Date
    Nov 2002
    Posts
    272
    I think I found another problem:

    while
    not(substring(@FieldsNameStr, @i, 1) = ',')
    or
    (@i < Len(@FieldsNameStr))

    If a comma is encountered, the second of your condition will still be true. So your inner loop will not end; at least not before the end of @FieldsNameStr is reached.

  8. #8
    Join Date
    Nov 2005
    Posts
    5
    I changed it & corrected its loop , it works for one column but it has problem about this part for more than 1:
    EXEC
    ('CREATE TABLE [dbo].[' + @TableName + '] ( '+ @TempStr +') ')


    Code:
    CREATE PROCEDURE [dbo].[CreateMyTable] ( @FieldsNameStr varchar(2000),
                                                                                 @FieldsTypeStr varchar(2000),
                                                                                 @TableName varchar(2000) )
    
    
    AS
    declare  @i int
    declare  @j int
    declare  @TempStr varchar(200)
    declare  @TempStr1 varchar(200)
    declare  @TempStr2 varchar(200)
    
    
    set @i = -1
    set @j = -1
    set @TempStr = ''
    
    WHILE (@i < Len(@FieldsNameStr)) and (@j < Len(@FieldsTypeStr))
    begin
    
      set @TempStr1 = ''
      declare @tt varchar(1)
       set @tt=substring(@FieldsNameStr, @i, 1)
    
      while  not(@tt = ',') and (@i < Len(@FieldsNameStr))
      begin
        set @i = @i +1
        set @TempStr1 = @TempStr1 + substring(@FieldsNameStr, @i, 1)
    --    print @TempStr1+'0'
      end
    
    
      set @TempStr2 = ''
      declare @rr varchar(1)
      set @rr=substring(@FieldsTypeStr, @j, 1)
      while  not(@rr= ',') and (@j < Len(@FieldsTypeStr))
      begin
       set @j = @j +1  
       set @TempStr2 = @TempStr2 + substring(@FieldsTypeStr, @j, 1)
    --   print @TempStr2+'1'
      end
    
      set @TempStr = @TempStr + '[' +@TempStr1+ ']' + @TempStr2 +' NOT NULL ,'
    --  print @TempStr+'3'
      set @i = @i +1
      set @j = @j +1
    end
    
    
    EXEC
      ('CREATE TABLE [dbo].[' + @TableName + '] ( '+ @TempStr +') ')
    GO

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why don't yo supply us with what the unput is suppose to be, and what the @TableName and @TempString are suppose to be....


    And you'd probably be better off posting this in the SQL Server forum
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by DonAmir
    I changed it & corrected its loop , it works for one column but it has problem about this part for more than 1:
    EXEC
    ('CREATE TABLE [dbo].[' + @TableName + '] ( '+ @TempStr +') ')
    As I wrote before, @TempStr ends in a comma. Frankly, I'm surprised it works for one column.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •