Replacing a reserved keyword - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Replacing a reserved keyword

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2014-02-11 : 12:29:12

Hi all,

I've got a sproc that we are migrating from SS2K that uses the reserved keyword 'Pivot'. Ideas please about the best way to replace it. I think it is renameable until the select from TempDB (which is mysterious as I can't see a table in tempDB by that name!!) as below...

CREATE PROCEDURE [dbo].[sp_CreateXtab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@SQLOutput varchar(8000) OUTPUT

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 ORDER BY ' + @pivot)

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)
--print @SQL

SELECT @sql = @sql + ' INTO tblIncentiveSummaryTemp '
--print @Select
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
--print @Select

--EXEC (@select)
SET @SQLOutput = @Select

SET ANSI_WARNINGS ON

Thanks,

JB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-11 : 12:49:01

You can just use square brackets around reserved names: [pivot]. Though I hate it, I've got plenty of databases that use reserved names as column names. We are using SQL Server 2005, 2008, 2008 R2, and 2012. Square brackets is the solution around this, but my opinion is that you should rename the columns so that you don't have to use square brackets. I don't have control over the names in my environments.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2014-02-11 : 14:46:09

Thanks Tara.

But...do any of us have any control? I really don't know why the word 'Administrator' is in our titles, as in fact we're usually just 'Database Manipulators'. Pardon the cynicism...

:)

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2014-02-11 : 14:46:17

Thanks Tara.

But...do any of us have any control? I really don't know why the word 'Administrator' is in our titles, as in fact we're usually just 'Database Manipulators'. Pardon the cynicism...

:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources