1. Registered User
Join Date
Nov 2005
Posts
5

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. Registered User
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. Registered User
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. Registered User
Join Date
Nov 2005
Posts
5
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. Registered User
Join Date
Nov 2005
Posts
5
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. Registered User
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. Registered User
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. Registered User
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. Window Washer
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

10. Registered User
Join Date
Nov 2002
Posts
272
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
•