Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    33

    Unhappy Unanswered: Varchar exceed 8000 issue

    I was trying to build a string based on one field from one table in Stored Procedure. I know if I declare A VARCHAR variable for the string, it won’t allow to exceed 8000. If does, it will truncate the reminding parts of the fields. What might be the simple solution for that? Even though I know I might need to use several substrings but how?
    Thanks!

    J827




    Declare @sql varchar 8000

    Select @sql =@sql + convert(varchar(50),fieldName) from tableA where conditions …

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Is using TEXT datatype an option ???
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well your code as it is will return null each and every time.

    What are you trying to accomplish?
    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.

  4. #4
    Join Date
    Mar 2004
    Posts
    33

    Unhappy varchar exceed 8000

    For whatever reason, my 'pivot' table records are quite big and it exceeded 8000 for @select/@sql arguments. it truncated and failed to execute.

    J827



    ================================================== ========
    CREATE PROCEDURE crosstab
    @select varchar(8000),
    @sumfunc varchar(100),
    @pivot varchar(100),
    @table varchar(100)
    AS

    DECLARE @sql varchar(8000), @delim varchar(1)
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF

    EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
    EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
    + @pivot + ' Is Not Null')

    SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

    SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
    WHEN 0 THEN '' ELSE '''' END
    FROM tempdb.information_schema.columns
    WHERE table_name='##pivot' AND column_name='pivot'

    SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
    stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
    + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

    DROP TABLE ##pivot

    SELECT @sql=left(@sql, len(@sql)-1)
    SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

    EXEC (@select)
    SET ANSI_WARNINGS ON

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What' sthe goal here?

    To generate DML based on the catalog?
    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.

  6. #6
    Join Date
    Mar 2004
    Posts
    33

    varchar exceed 8000

    e.g. it will PRINT the following for @select


    select pub_name, count(qty) as orders, sum(qty) as total , 'business ' = sum( CASE type WHEN 'business ' THEN qty END), 'mod_cook ' = sum( CASE type WHEN 'mod_cook ' THEN qty END), 'popular_comp' = sum( CASE type WHEN 'popular_comp' THEN qty END), 'psychology ' = sum( CASE type WHEN 'psychology ' THEN qty END), 'trad_cook ' = sum( CASE type WHEN 'trad_cook ' THEN qty END), 'UNDECIDED ' = sum( CASE type WHEN 'UNDECIDED ' THEN qty END) from sales inner join titles on (sales.title_id=titles.title_id) right join publishers on (publishers.pub_id=titles.pub_id) group by pub_name
    ================================================== ========



    DECLARE @select varchar(8000)
    DECLARE @sumfunc varchar(100)
    DECLARE @pivot varchar(100)
    DECLARE @table varchar(100)

    set @select ='select pub_name, count(qty) as orders, sum(qty) as total from sales inner join titles on (sales.title_id=titles.title_id) right join publishers on (publishers.pub_id=titles.pub_id) group by pub_name'
    set @sumfunc = 'sum(qty)'
    set @pivot = 'type'
    set @table = 'titles'

    DECLARE @sql varchar(8000), @delim varchar(1)
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF

    EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
    EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
    + @pivot + ' Is Not Null')

    SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )


    SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) WHEN 0 THEN '' ELSE '''' END
    FROM tempdb.information_schema.columns
    WHERE table_name='##pivot' AND column_name='pivot'

    SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', '
    FROM ##pivot



    DROP TABLE ##pivot

    SELECT @sql=left(@sql, len(@sql)-1)



    SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')


    print @select
    return


    EXEC (@select)
    SET ANSI_WARNINGS ON

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...what I would do is have a bumnch of unions.

    Each segement would be a sql group

    I would create a view

    I would then bcp out the view to a *.sql file..

    I would then call osql and execute the output and redirect the output to a *.bat file...

    Thinking about it you could turn the sql in to a create view

    Code:
    SELECT SQL FROM (
    SELECT ' SELECT ' AS SQL
    	, TABLE_NAME
    	, 1 AS SQL_GROUP
    	, 1 AS ROW_ORDER
    FROM INFORMATION_SCHEMA.TABLES
    UNION ALL
    SELECT '	    ' + COLUMN_NAME  AS SQL
    	, TABLE_NAME
    	, 2 AS SQL_GROUP
    	, 1 AS ROW_ORDER
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE ORDINAL_POSITION = 1
    UNION ALL
    SELECT '	  , ' + COLUMN_NAME  AS SQL
    	, TABLE_NAME
    	, 2 AS SQL_GROUP
    	, ORDINAL_POSITION AS ROW_ORDER
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE ORDINAL_POSITION <> 1
    UNION ALL
    SELECT '   FROM    ['+TABLE_NAME+']'  AS SQL
    	, TABLE_NAME
    	, 3 AS SQL_GROUP
    	, 1 AS ROW_ORDER
    FROM INFORMATION_SCHEMA.TABLES
    UNION ALL
    SELECT 'GO'  AS SQL
    	, TABLE_NAME
    	, 4 AS SQL_GROUP
    	, 1 AS ROW_ORDER
    FROM INFORMATION_SCHEMA.TABLES
    ) AS XXX
    ORDER BY TABLE_NAME
    	, SQL_GROUP
    	, ROW_ORDER
    That what I would do...
    Last edited by Brett Kaiser; 06-14-04 at 17:07.
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well that's my point

    If you turn it in to a createw view v_cross_tablename as

    And you know the standard you sproc can build the view, do the select against, then drop the view....
    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.

  9. #9
    Join Date
    Mar 2004
    Posts
    33

    Smile

    got the solution from the following link:

    http://www.sqlteam.com/Forums/topic....16&whichpage=3

Posting Permissions

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