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 |
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-09 : 02:40:05
|
I'm trying to mess around with the crosstab proc fromhttp://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tablesbut I'm getting the error,Msg 156, Level 15, State 1, Procedure crosstab, Line 23Incorrect syntax near the keyword 'pivot'.And its pointing to the part of the code that is...SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + the code that I'm pasting into sql server 2005 is...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 Anyone have any ideas? i cant seem to find the error |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 02:48:18
|
Did you get the error while creating procedure or while executing?If latter, what were values passed for params? |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-09 : 02:52:40
|
quote: Originally posted by visakh16 Did you get the error while creating procedure or while executing?If latter, what were values passed for params?
I got it trying to create the procedure |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 02:58:39
|
one reason can be because pivot is a keyword in sql 2005. try wrapping it within [].use [pivot] instead of pivot |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-09 : 14:02:12
|
quote: Originally posted by visakh16 one reason can be because pivot is a keyword in sql 2005. try wrapping it within [].use [pivot] instead of pivot
I changed two instances of pivot to [pivot], you can see it hereSELECT @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 And it ran successfully, the proc "crosstab" is now created.However when I try to execute the crosstab with my table I get this error message,Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'pivot'.Msg 208, Level 16, State 1, Line 1Invalid object name '##pivot'.Msg 208, Level 16, State 1, Procedure crosstab, Line 23Invalid object name '##pivot'.I'm looking at the code now, and I think there are more problems syntax wise in the proc code but I can't seem to figure it out, should I rename pivot to something else completely?da_rate is nvarcharda_date is smalldatetimeexecutiontime is nvarchar |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 14:25:41
|
change this line too and tryEXEC ('SELECT ' + @pivot + ' AS [pivot] INTO ##pivot FROM ' + @table + ' WHERE 1=2') |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-09 : 15:37:43
|
quote: Originally posted by visakh16 change this line too and tryEXEC ('SELECT ' + @pivot + ' AS [pivot] INTO ##pivot FROM ' + @table + ' WHERE 1=2')
Great! It worked, When I run the following code everything is working well, execute crosstab 'select da_date from greatwolfcleanedup group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' however when I add some where clauses like suchexecute crosstab'select da_date from greatwolfcleanedup where location like '%grand mound%' and roomtype like '%kbsx%' group by da_date order by da_date','min(da_rate)', 'executiontime', 'greatwolfcleanedup' I get the following error message, Msg 102, Level 15, State 1, Line 2Incorrect syntax near '%'.Pointing to the where statement, I've run the select statement by itself in a separate query and it seems to be fine, Is there something that I'm overlooking? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-09 : 15:38:53
|
execute crosstab'select da_date from greatwolfcleanedup where location like ''%grand mound%'' and roomtype like '%kbsx%' group by da_date order by da_date', E 12°55'05.25"N 56°04'39.16" |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-09 : 15:44:38
|
quote: Originally posted by Peso execute crosstab'select da_date from greatwolfcleanedup where location like ''%grand mound%'' and roomtype like '%kbsx%' group by da_date order by da_date', E 12°55'05.25"N 56°04'39.16"
I see, thank you so much, I would have never noticed. It has to be in those double single quotes because its enclosed inside another single quote? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-09 : 17:40:24
|
Learn your string delimiters!execute crosstab 'select da_date from greatwolfcleanedup where location like ' + quotename('%' + @hotel + '%', '''') + ' and roomtype like ' + quotename('%' + @roomtype + '%', '''') + ' and da_date >= ''' + convert(varchar(25), @datebegin, 112) + ''' and da_date < ''' + convert(varchar(25), dateadd(day, 1, @dateend), 112) + ''' group by da_date order by da_date' 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' E 12°55'05.25"N 56°04'39.16" |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-09 : 17:42:10
|
I created another stored proc that executes this stored proc (so the user doesn't have to always type in the entire select statement each time)using this codecreate procedure gw @hotel varchar(200), @roomtype varchar(30), @datebegin smalldatetime, @dateend smalldatetimeas beginexecute crosstab'select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' and da_date >= @datebegin and da_date < dateadd (d, +1, @dateend) group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' end I'm trying to run this proc by...execute gw 'grand mound', 'kbsx', '7/4/2008', '7/25/2008' but its giving the errorMsg 137, Level 15, State 2, Line 1Must declare the scalar variable "@hotel".I thought I was declaring that variable in the first parameter where i typed'grand mound'Does anyone know what I'm doing wrong? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-09 : 17:43:31
|
Here it is, again, because you deleted your other postexecute crosstab 'select da_date from greatwolfcleanedup where location like ' + quotename('%' + @hotel + '%', '''') + ' and roomtype like ' + quotename('%' + @roomtype + '%', '''') + ' and da_date >= ''' + convert(varchar(25), @datebegin, 112) + ''' and da_date < ''' + convert(varchar(25), dateadd(day, 1, @dateend), 112) + ''' group by da_date order by da_date' 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' E 12°55'05.25"N 56°04'39.16" |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-09 : 17:54:01
|
quote: Originally posted by Peso Here it is, again, because you deleted your other postexecute crosstab 'select da_date from greatwolfcleanedup where location like ' + quotename('%' + @hotel + '%', '''') + ' and roomtype like ' + quotename('%' + @roomtype + '%', '''') + ' and da_date >= ''' + convert(varchar(25), @datebegin, 112) + ''' and da_date < ''' + convert(varchar(25), dateadd(day, 1, @dateend), 112) + ''' group by da_date order by da_date' 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' E 12°55'05.25"N 56°04'39.16"
I ended up doing this execute crosstab'select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' and da_date >= @datebegin and da_date < dateadd (d, +1, @dateend) group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' is there anything wrong with it?I tried to do yours instead, but it came back with Msg 102, Level 15, State 1, Procedure gw2, Line 9Incorrect syntax near '+'. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-10 : 02:42:35
|
shouldnt this be like this:-execute crosstab'select da_date from greatwolfcleanedup where location like ''%'''+@hotel+'''%'' and roomtype like ''%'''+@roomtype+'''%'' and da_date >='' + @datebegin and da_date < ''+ dateadd (d, +1, @dateend) +'' group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-10 : 04:18:59
|
Yes, there are a lot of problems with that approach.Use QUOTENAME function to remedy all sorts of escape characters.execute crosstab 'select da_date from greatwolfcleanedup where location like ' + quotename('%' + @hotel + '%', '''') + ' and roomtype like ' + quotename('%' + @roomtype + '%', '''') + ' and da_date >= ''' + convert(varchar(25), @datebegin, 112) + ''' and da_date < ''' + convert(varchar(25), dateadd(day, 1, @dateend), 112) + ''' group by da_date order by da_date' 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' E 12°55'05.25"N 56°04'39.16" |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-10 : 13:42:11
|
I can't sucessfully create either procedures with those two queries, for both of them I get Incorrect syntax near '+'. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-10 : 13:48:18
|
quote: Originally posted by fuzzyip I can't sucessfully create either procedures with those two queries, for both of them I get Incorrect syntax near '+'.
but you told earlier you created it successfully and error is only in executing?? |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-10 : 14:00:31
|
quote: Originally posted by visakh16
quote: Originally posted by fuzzyip I can't sucessfully create either procedures with those two queries, for both of them I get Incorrect syntax near '+'.
but you told earlier you created it successfully and error is only in executing??
Yes, I used mine to create itcreate procedure gw @hotel varchar(200), @roomtype varchar(30), @datebegin smalldatetime, @dateend smalldatetimeas beginexecute crosstab'select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' and da_date >= @datebegin and da_date < dateadd (d, +1, @dateend) group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' end The above executes, but I get this...execute gw 'grand mound', 'kbsx', '7/4/2008', '7/25/2008'output:Msg 137, Level 15, State 2, Line 1Must declare the scalar variable "@hotel".You and Peso gave me different code to create the proc so I figured my coding was the reason for the error, but when I try to run both of yours code it gives me that + syntax error. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-10 : 14:08:34
|
aha...so you're trying to call crosstab procedure from another procedure. The error is because the variable @hotel is not inside cross tab stored procedure. you probably need to do like thiscreate procedure gw @hotel varchar(200), @roomtype varchar(30), @datebegin smalldatetime, @dateend smalldatetimeas begindeclare @sql varchar(8000)set @sql='select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' +' and da_date >= '+ @datebegin +' and da_date < '+ dateadd (d, +1, @dateend) +' group by da_date order by da_date 'execute crosstab @sql, 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' end |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-10 : 14:16:44
|
Am I talking to blind people?Use QUOTENAME. E 12°55'05.25"N 56°04'39.16" |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 2008-08-10 : 14:25:05
|
quote: Originally posted by visakh16 aha...so you're trying to call crosstab procedure from another procedure. The error is because the variable @hotel is not inside cross tab stored procedure. you probably need to do like thiscreate procedure gw @hotel varchar(200), @roomtype varchar(30), @datebegin smalldatetime, @dateend smalldatetimeas begindeclare @sql varchar(8000)set @sql='select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' +' and da_date >= '+ @datebegin +' and da_date < '+ dateadd (d, +1, @dateend) +' group by da_date order by da_date 'execute crosstab @sql, 'min(da_rate)', 'executiontime', 'greatwolfcleanedup' end
Using your code I getMsg 156, Level 15, State 1, Procedure gw3, Line 9Incorrect syntax near the keyword 'and'.Msg 105, Level 15, State 1, Procedure gw3, Line 11Unclosed quotation mark after the character string ' end'.I also tried changing JUST the select statement to what I was doing earlier that was successfully running, which actually did successfully complete, but upon execution, it still came back with Undeclared variable @hotel.ahhh |
|
|
Previous Page&nsp;
Next Page
|
|
|
|
|