Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Text data question

Author  Topic 

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-10-30 : 13:43:44
Anyone have any luck passing the value of a text column into a stored procedure?

Yes, it HAS to be a text variable I can pass to a stored procedure. I won't be able to SELECT it or READTEXT it within a sproc.

Yes, I DO have to keep it in a text column in a table (unless you can tell me how to manipulate a text VARIABLE).

Yes, I DO need to do this entirely within T-SQL (no ADO solutions please).

If I'm missing something obvious I expect to be slapped hard by the SQL Team bunch. Can't figure this out and it's pissing me off.

Any help is unbelieveably appreciated, you really cannot believe how much! Thanks.

suinfall
Starting Member

1 Post

Posted - 2004-06-14 : 12:10:51
For whatever reason, my 'pivot' table records are quite big and it exceeded 8000 for @select argument. it truncated and failed to execute. Please help!

Suinfall



================================================== ========
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
Go to Top of Page
  Previous Page&nsp; 

- Advertisement -