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.
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) ASDECLARE @sql varchar(8000), @delim varchar(1)SET NOCOUNT ONSET ANSI_WARNINGS OFFEXEC ('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 ##pivotDROP TABLE ##pivotSELECT @sql=left(@sql, len(@sql)-1)SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')EXEC (@select)SET ANSI_WARNINGS ON |
|
|
Previous Page&nsp;
|
|
|
|
|