cfstoredproc fails yet SP runs in QA

ok I'm stumped...

I've got a SP I found which I've modified slightly that dynamically creates a pseudo-crosstab


The Procedure is

------------------------------------------------------------
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 ),0)' )


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) + ''' = ' +
'isnull(' + 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)
------------------------------------------------------------

The statement it creates is

select ProductIDUPC ,
'669' = isnull(sum( CASE OrderID WHEN 167669 THEN qty END ),0),
'981' = isnull(sum( CASE OrderID WHEN 167981 THEN qty END ),0),
'135' = isnull(sum( CASE OrderID WHEN 168135 THEN qty END ),0),
'686' = isnull(sum( CASE OrderID WHEN 168686 THEN qty END ),0),
'022' = isnull(sum( CASE OrderID WHEN 169022 THEN qty END ),0)
from vwDistributorProductionForecast
group by ProductIDUPC Order By ProductIDUPC asc

The SP runs fine in query Analyzer and give the expected results
                      669     981   135    686    022
92013300249      0      76      0      38      38
92013300307      0       0      0      19      0
92013300321      19      0      0      76      0
92013300362      0      0      0      61      0
92013300364      19      0      0      57      0

(i've modified the orderids for readablity)

If I manually copy the select query it runs fine in cf

but when I call the SP using cfstoredproc

<cfstoredproc datasource="Mydata" procedure="crosstab"
                    debug="yes"
                  returncode="0">

<cfprocparam type = "IN"
  variable = "select"
  value = "select ProductIDUPC from vwDistributorProductionForecast group by ProductIDUPC Order By ProductIDUPC asc"
  CFSQLType = "CF_SQL_VARCHAR"
  maxLength = "8000"
   null = "No">

<cfprocparam type = "IN"
  variable = "sumfunc"
  value = "sum(qty)"
  CFSQLType = "CF_SQL_VARCHAR"
  maxLength = "100"
   null = "No">

<cfprocparam type = "IN"
  variable = "pivot"
  value = "OrderID"
  CFSQLType = "CF_SQL_VARCHAR"
  maxLength = "100"
   null = "No">

<cfprocparam type = "IN"
  variable = "table"
  value = "vwDistributorProductionForecast"
  CFSQLType = "CF_SQL_VARCHAR"
  maxLength = "1000"
   null = "No">

<cfprocresult name = spResults>
</cfstoredproc>


I get the error

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'END'.

Any ideas as to what is going on here...this SHOULD work









LVL 36
SidFishesAsked:
Who is Participating?
 
73SpyderCommented:
The approach I have taken on antoher page was to take the SQL code and put it into a cfc.

There I ran 2 queries, one to get the list of items for the column names.

Then I built the 2nd query using the result set of the first.  The end result was the same as the sproc for the cross tab, but


select ProductIDUPC ,
<cfoutput query="get_list">
isnull(sum( CASE OrderID WHEN '#expression_check_calue#' THEN qty END ),0),  AS [#col_value#]

</cfquery>
, last_variable
from vwDistributorProductionForecast
group by ProductIDUPC Order By ProductIDUPC asc


The other side effect was the query ran much faster, especially when the get_list query was larger, than it did using the crosstab sproc we use
0
 
73SpyderCommented:
The only I see at first glance is

here you have a length of 1000  

<cfprocparam type = "IN"
  variable = "table"
  value = "vwDistributorProductionForecast"
  CFSQLType = "CF_SQL_VARCHAR"
  maxLength = "1000"
   null = "No">

but in the sproc you have this   @table varchar(100)

0
 
73SpyderCommented:
Aslo when I call out crosstab sproc I had to modify the cfparam tag to this

<cfprocparam type="In" dbvarname="@TABLE" value="#variables.temp_table#" cfsqltype="CF_SQL_VARCHAR">

to use dbvarname="@TABLE" instead of  variable = "table"
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
SidFishesAuthor Commented:
thx...but those had no effect

The weird thing is that I can remove all of the cfprocparam's

<cfstoredproc datasource="kdata" procedure="crosstab"
                    debug="yes"
                  returncode="0">

</cfstoredproc>


and it still throws the error which leads to think that somethings happening in the sp compile which is unrelated to the cf code ...(since it's not even getting to the point of passing the params)

0
 
SidFishesAuthor Commented:
err...wait...no that's not right I do need the procparams...

(i've tried so many thing I'm getting confused...)

0
 
bwasyliukCommented:
Have you tried running the Profiler as the page executes, to see what is being passed in to the stored procedure?
0
 
SidFishesAuthor Commented:

I ended up going a different route
I used the  SIMPLEXtab SP from
http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html

then used cfquery instead of cfstoredproc

<cfquery name="getrows" datasource="kdata">
      exec simplextab @XField = 'OrderID', @XTable = 'vwDistributorProductionForecast',
      @XWhereString = ' <cfif varOrderIDLISt  NEQ "">#varOrderIDLISt#</cfif>', @XFunction = 'Sum', @XFunctionField = 'qty', @XRow = 'Productidupc'
</cfquery>

as far as I can tell cfstoredproc doesn't like the dynamic sql as both the SP in the original q and in the solution failed using cfstoredproc

cfquery grabs the RS no problem

btw 73

re: <cfprocparam type="In" dbvarname="@TABLE" value="#variables.temp_table#" cfsqltype="CF_SQL_VARCHAR">

according to Livedocs

"Changed the dbvarname attribute behavior: it is now ignored for all drivers. ColdFusion MX uses JDBC 2.2 and does not support named parameters."

and

"ColdFusion MX supports positional parameters only and you must code cfprocparam tags in the same order as the associated parameters in the stored procedure definition."

http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=00000313.htm


I'll award 73 the points for the effort



0
 
73SpyderCommented:
Thanks for the points, We use BlueDragon,  I'll have to look up the specs to see if I am coding stuff the best way
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.