change variable type from varchar to nvarchar

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 2005 Forums

Transact-SQL (2005)

change variable type from varchar to nvarchar

Author

Topic

vtkquyen
Starting Member

8 Posts

Posted - 2009-05-07 : 14:14:10

CREATE PROCEDURE crosstab
@select nvarchar(4000),
@sumfunc nvarchar(100),
@pivot nvarchar(100),
@table nvarchar(100),
@where nvarchar(1000)=null
AS

DECLARE @sql nvarchar(4000), @delim nvarchar(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 '
+ IsNull(@where,'1=1') + ' AND ' + @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(nvarchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(nvarchar(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

i use crosstab and i change variable type from varchar to nvarchar , my purpose to show unicode but when show data is not ok
if anybody worked with it, please help me
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-11 : 01:56:13

replace

exec

with

sp_executesql

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources