?
Solved

cfstoredproc fails yet SP runs in QA

Posted on 2006-05-31
8
Medium Priority
?
467 Views
Last Modified: 2013-12-24
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









0
Comment
Question by:SidFishes
  • 4
  • 3
8 Comments
 
LVL 9

Expert Comment

by:73Spyder
ID: 16800326
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
 
LVL 9

Expert Comment

by:73Spyder
ID: 16800360
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
 
LVL 36

Author Comment

by:SidFishes
ID: 16800571
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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 36

Author Comment

by:SidFishes
ID: 16800584
err...wait...no that's not right I do need the procparams...

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

0
 
LVL 9

Accepted Solution

by:
73Spyder earned 1500 total points
ID: 16801248
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
 
LVL 7

Expert Comment

by:bwasyliuk
ID: 16802446
Have you tried running the Profiler as the page executes, to see what is being passed in to the stored procedure?
0
 
LVL 36

Author Comment

by:SidFishes
ID: 16803462

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
 
LVL 9

Expert Comment

by:73Spyder
ID: 16806173
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month16 days, 8 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question