August 13, 2003 at 12:23 pm
Hi all,
I need to calculate the row totals and the maximum row total from a table that continuesly changes its columns (there is one column per active order in the job shop).
I use the following statement to collect all numeric columns into a string and then use that string within an execute('...') - statement.
Works fine as long as the resulting string doesn't exceed 4000 characters. It's truncated at that lenth although the variable @NumericColsAdded is declared as varchar(8000).
Is there any restriction to the Coalesce-function or what's wrong here?
thanks for your help
--------------------------------------
declare @NumericColsAdded varchar(8000)
select @NumericColsAdded = Coalesce(@NumericColsAdded + '+', '') + c.name
FROM sysobjects o
INNER JOIN syscolumns c ON (o.id = c.id and o.xtype = 'U')
INNER JOIN systypes t ON c.xusertype = t.xusertype
where o.name = 'JobShop_RessourceDemand'
and t.name in ('numeric','decimal',
'bigint','int','smallint','tinyint',
'float','real')
and c.name != 'UsableCapacity'
select @NumericColsAdded
August 13, 2003 at 11:09 pm
Have you tried.
sp_help <table_name>
It will return two recordsets. The second recordset will return you column details.
I am not sure whether this will help u?
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 14, 2003 at 1:55 am
sp_help gives me one column with all column names, which is exactly the information I collect from syscolumns.
thanks anyway
quote:
Have you tried.sp_help <table_name>
August 14, 2003 at 5:09 am
I had the same problem in a procedure that created audit triggers, declare 8000, only works to 4000. I think the obviuos cause is that somewhere inside there is an implicit conversion to unicode even though it's declared varchar not nvarchar.
What I did rather than pursue it was to divide them in half into @Str1 and @Str2, then found that it worked just fine when I wanted to do an Execute(@Str1 + @Str2).
Don't know how you partition them in your case, if they are somewhat randomly named just split them based on < and >= 'M'.
I didn't have a table big enough to try it, but could Coalesce be the thing introducing the 4000 limit? Did you try setting @NumericColsAdded ='' first and leaving that out, then stripping off the first "+"?
August 14, 2003 at 5:55 am
Could not reproduce on SQL 2000.
Would you mind to try the following :
Create Table TTest(AA int,jnk varchar(10),BB int,CC float,DD tinyint,EE real,jnkII char(10))
GO
declare @NumericColsAdded varchar(8000),
@PLUS varchar(1),-- Varchar(1) Shocking!
@Noth Varchar(1)
Set @Noth=''
Set @PLUS='+'
Select @NumericColsAdded = Coalesce(@NumericColsAdded + @PLUS, @noth) +
Cast(Column_Name as Varchar(64)) -- nvarchar(128)
FROM INFORMATION_SCHEMA.COLUMNS
where Table_Name = 'TTest' and DATA_TYPE in ('numeric','decimal','bigint','int','smallint','tinyint','float','real')
Select @NumericColsAdded
Select DataLength(@NumericColsAdded),Len(@NumericColsAdded)
GO
Drop Table TTest
GO
August 14, 2003 at 8:16 am
Allright, the trick is to cast the column name as varchar, then it works fine beyond 4000 characters, no matter whether you use the systables or the information_schema views. Also, declaring '+' and '' as variables has no effect.
The problems really seems to be the internal unicode.
thanks for the help
quote:
Could not reproduce on SQL 2000.Select @NumericColsAdded = Coalesce(@NumericColsAdded + @PLUS, @noth) +
Cast(Column_Name as Varchar(64)) -- nvarchar(128)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply