| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-06-05, 15:27
|
|
Registered User
|
|
Join Date: Nov 2005
Posts: 5
|
|
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
|
|
11-07-05, 02:52
|
|
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.
|
|
11-07-05, 02:54
|
|
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.
|
|
11-07-05, 07:22
|
|
Registered User
|
|
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?
|
|
11-07-05, 07:25
|
|
Registered User
|
|
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.
|
|
11-07-05, 08:07
|
|
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.
|
|
11-07-05, 08:15
|
|
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.
|
|
11-07-05, 16:25
|
|
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
|
|
11-08-05, 09:16
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,303
|
|
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
|
|
11-09-05, 03:01
|
|
Registered User
|
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
please help me to correct its infinity loop(Dynamic Create Table in Stored Procedure)
