Error with stored procedure

I have the following code in my code behind page:

 Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
 Dim CM As New SqlCommand("spCCF_CrossTab", CN)
 CM.CommandType = CommandType.StoredProcedure
 CM.Parameters.Add(New SqlParameter("@.LocationID", "CCFIF"))
 CM.Parameters.Add(New SqlParameter("@.BeginDate", dtbStart.Text))
 CM.Parameters.Add(New SqlParameter("@.EndDate", dtbEnd.Text))
 CN.Open()
 DR = CM.ExecuteReader(CommandBehavior.CloseConnection)
 dgReport.DataSource = DR
 dgReport.DataBind()

A SQL exception is thrown: Incorrect syntax near the keyword 'END'

But I turned on tracing in Enterprise Manager, the following request is sent to SQL:

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'

In query analyzer the above line executes without error and returns the expected information.

My stored procedure is:

CREATE PROCEDURE spCCF_CrossTab
@.LocationID varchar(10),
@.BeginDate varchar(10),
@.EndDate varchar(10)
AS
declare @.select varchar(8000), @.sumfunc varchar(100), @.pivot varchar(100), @.table varchar(100), @.where varchar(1000)
select @.select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.Productivity_CCF.[Date] BETWEEN CONVERT(DATETIME, ''' + @.BeginDate + ''', 101) AND CONVERT(DATETIME, ''' + @.EndDate + ''', 101)) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name order by Action'
select @.sumfunc= 'COUNT(ActionID)'
select @.pivot='UserName'
select @.table= 'UserInfo'
select @.where='(dbo.UserInfo.LocationID = ''' + @.LocationID + ''' and dbo.UserInfo.Inactive<>1 )'
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 and ' + @.where)
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)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')
EXEC (@.select)
SET ANSI_WARNINGS ON
GO

I've been banging my head on this for quite some time now, any insight someone might have as to the problem would be greatly appreciated! Thanks!I don't see the ELSE and END part of the CASE statement in the code below.

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', 'should be something more here? FROM ##pivot

But that doesn't explain why it works in Query Analyzer but not through code.|||I'm using the code from this article: http://www.sqlteam.com/item.asp?ItemID=2955
which I have seen recommended before on the forums so I'm assuming someone has gotten this to work.

I've added a few things but have left everything after the statement below unchanged.
<code>
SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, 'END)')
<code
Again, it does work successfully in query analyzer and SQL trace does not appear to be generating any errors.|||The code in the article runs fine for me against the Pub database. Yes, many people have gotten it to work.

But you are not running the exact code from the article, so that's a moot point.

My suggestion would be to specify the datatype of your parameters explicitly in your ASP.NET code. It looks like ASP.NET is assuming nVarchar. I don't know if that makes a difference, but I would try that. Additionally, I would add an Output parameter to the stored procedure and pass back the @.select variable to your ASP.NET page and have your ASP.NET page display it. This will show you exactly what the stored procedure is trying to execute and might help your debugging effort.

Terri|||The stored procedure runs fine for me when in Query Analzyer. The problems is when I'm trying to call it from the ASP page.

I removed my modifications from the SP and am still running into the same exact error on the aspx page. I am already explicity setting the datatype for the parameters. Setting an output parameter in the SP is doing nothing in my debug efforts since it never gets to that point in the code and is erroring prior.

The error my aspx page is throwing a SQL exception: Incorrect syntax near the keyword 'END'.

I'm still trudging away at this and am not getting any closer to solving the problem. Any other insight on this problem?
Thanks!|||We need to see what @.select holds when your stored procedure goes to EXEC it.

There are a few ways to do this. Can you add an OUTPUT parameter to your stored procedure to hold @.select, and then output the value of this parameter on your page and then let us know what it is?

Terri|||Can you tell me how to get the output parameter to return to the aspx page as I'm getting an error when trying to do so? i have the following:

...
arParms(3) = New SqlParameter("select", SqlDbType.NVarChar, 8000)
arParms(3).Direction = ParameterDirection.ReturnValue
...
Dim strReturn As String = arParms(3).ToString ' also tried response.write (arParms(3).Value)
Response.Write(strReturn)
SP: DECLARE @.sql varchar(8000)
and instead of exec @.select I have return @.select

Error I get: Syntax error converting the varchar value 'SELECT dbo.ActionCodes.Name AS Action , 'asmith ' = COUNT(ActionID), 'ashelts' = COUNT ... (and then too long of a select statement to write out the entire message).

In debugging I did notice that this line of code in the sp:

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, 'END)' )

was causing part of the problem because the select statement looked like:

SELECT dbo.ActionCodes.Name AS Action , 'asmith ' = COUNT(ActionID END)

So I removed the END, now I'm getting a new error but I can't get to completely write on the screen I get as SQL exception: The name 'SELECT dbo.ActionCodes... (part of the select statement). I've tried simplifying my query and it still is too long to display the entire error.

Thanks

On a frustrating side; What I still can't understand it WHY does the stored procedure work (with the parameters I have from the aspx page) in Query Analyzer?|||Ok - actually got the entire sql error finally:

The name 'SELECT dbo.ActionCodes.Name AS Action , 'asmith ' = COUNT(ActionID) FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name' is not a valid identifier.|||To get the value of @.select back to your ASP.NET page, I think you should have:

arParms(3) = New SqlParameter("@.select", SqlDbType.NVarChar, 8000)
arParms(3).Direction = ParameterDirection.Output

You shouldn't need to RETURN @.select in the stored procedure. Just comment out the EXEC @.select line so no errors are generated.

But I am afraid I am leading you astray. If your stored procedure was running correctly from query analyzer with the desired parameters, then your stored procedure should not need any revisions.

Is this still returning the correct results from Query Analyzer?

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'

If so, can you temporarily forego the Command parameters and just put that exec statement into your ASP.NET page? (untested)

Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
Dim CM As New SqlCommand("exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'", CN)
CM.CommandType = CommandType.Text
CN.Open()
DR = CM.ExecuteReader(CommandBehavior.CloseConnection)
dgReport.DataSource = DR
dgReport.DataBind()

Does this return the expected result?

Terri|||No. Query analyzer doesn't like this either (error 203 invalid identifier). HOWEVER, when I put this line back in the SP with the word 'END'

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' End )' )

Query analyzer works just fine with:

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'11/3/2003', @.EndDate = N'11/4/2003'

What the aspx page does:
without the word End I get error 203 invalid identifier.
With End I get the syntax error again "Incorrect syntax near the keyword 'End'."

The problem is I have to have about 8 different aspx pages that are going to need similar cross tab type reports - and I want to make the pages and code so I don't have to modify the aspx pages everytime there is a staff change. I can handle simple stored procedures but I really don't understand what is going on in this one enough to trouble shoot very well.

I appreciate your help in trying to get this worked out.|||For grins and giggles, what happens when you present the dates in ISO format?

exec spCCF_CrossTab @.LocationID = N'CCFIF', @.BeginDate = N'20031103', @.EndDate = N'20031104'

Terri|||Tried that - still an error.
I decided to start from scratch - took just the code for the stored procedure and added a simple select statement.

Still the same darn error with the END. Removing it - I get a cross tab table - but the values are a sum of all the values for that row - I get a table that each row has the same value in each column. I see the importance of the END, have been reading up on the stuff function, but still can't get this to work.

Since I don't have a DBA accessible, I may have to (ugh!) go to a case statement and figure out how I will manage changing staff.|||If you are still having trouble, script out all of your tables, and provide some data via a series of INSERT statements. This one is really tough to help with without having the real "stuff" to mess with.

Terri|||I was able to make the scripts for the tables, but is there a tool for doing the inserts?
I found some different code for doing the cross tabs. Does the same thing: works fine from QA, but get a SQL syntax error "Incorrect syntax at keyword 'END'" from the aspx page.

I tried this on a sample database with sample code provided (copied the code, didn't even retype) - same exact situation: works in query analyzer, syntax error with the aspx page.

I'm really doubting the problem is in the code since I have had a number of circumstances where this works in QA. Could the problem be in the version of the framework or .NET? I'm using Visual Studio 2002, 1.0 of the framework.

CREATE PROCEDURE crosstabextended
@.select_stmt varchar(8000),
@.groupfn varchar(100),
@.pivot_column varchar(100),
@.output_table varchar(100),
@.select_table varchar(100)
AS
DECLARE @.sql varchar(8000)
DECLARE @.delimiter varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DROP TABLE ##pivot
EXEC ('SELECT ' + @.pivot_column + ' AS pivot INTO ##pivot FROM ' + @.select_table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot_column + ' FROM ' + @.select_table + ' WHERE '
+ @.pivot_column + ' Is Not Null' )
-- Add the string END to the group function (@.groupfn) variable
SELECT @.sql='', @.groupfn=stuff(@.groupfn, len(@.groupfn), 1, ' END)' )
--Check if pivot column starts with char or date
SELECT @.delimiter=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(@.groupfn,CHARINDEX( '(', @.groupfn )+1, 0, ' CASE ' + @.pivot_column + ' WHEN '
+ @.delimiter + CONVERT(varchar(100), pivot) + @.delimiter + ' THEN ' ) + ', ' FROM ##pivot
SELECT @.sql=LEFT(@.sql, LEN(@.sql)-1)
SELECT @.select_stmt=STUFF(@.select_stmt, CHARINDEX(' FROM ', @.select_stmt)+1, 0, ', ' + @.sql + ' ')
SELECT @.select_stmt=REPLACE(@.select_stmt, ' FROM', 'INTO '+ @.output_table + ' FROM')
IF EXISTS(SELECT table_name FROM tempdb.information_schema.tables WHERE table_name = @.output_table)
BEGIN
EXECUTE('DROP TABLE ' + @.output_table)
END
EXEC (@.select_stmt)
EXECUTE('SELECT * FROM ' + @.output_table)
SET ANSI_WARNINGS ON
GO

Code used in aspx page:

 Dim CN1 = New SqlConnection(ConfigurationSettings.AppSettings("TestString"))
 Dim CM As New SqlCommand("execute crosstabextended 'select Store_name as StoreName from storemaster inner join sales on (sales.store_id=storemaster.store_id) group by store_name', 'sum(qty)', 'toy_id', '##mytemp', 'toymaster'", CN1)
 CM.CommandType = CommandType.Text
CN1.Open()
 Dim da As SqlDataAdapter
 da = New SqlDataAdapter(CM)
da.Fill(dsData)

Thanks!|||Did you ever find a solution to this problem?