SQL server crosstab

theomysh
theomysh used Ask the Experts™
on
Hello Experts,

I have a stored procedure "crosstab" in SQL Server 2005 which looks like this...
/******************************************************************************
USE [DB_SAQ]
GO
/****** Object:  StoredProcedure [dbo].[crosstab]    Script Date: 08/05/2011 11:38:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[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)' )

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
/************************************************************************************

The following SQL...

SQL = "SELECT iAnnee, Sum(Pond_1) AS n_p, Count(0) AS n FROM tblDonnees WHERE (((iAnnee)=2011) AND ((QB2) Is Not Null) And ((QB2)<>0) AND ((Succ)= 210071) AND ((iVague)= 1)) GROUP BY iAnnee"

I send the following parameters to the following function...

getDataTableByProcedure "crosstab", SQL, "Pond_1", "QB2", "tblDonnees"

I get returned the following error...
      adapt.Fill(tmpTable)      Run-time exception thrown : System.Data.SqlClient.SqlException - Incorrect syntax near ')'.
Incorrect syntax near 'iAnnee'.      

This procedure works on other databases and my sql seems to be fine, can anyone tell me how I should alter my stored function?

This is my getDataTableByProcedure in vb.net......

Public Function getDataTableByProcedure(ByVal sql As String, ByVal Param1 As String, ByVal Param2 As String, ByVal Param3 As String, ByVal Param4 As String) As DataTable

        '     sql                param1                  param2                  param3              param4
        'EXECUTE crosstab      SELECT statement,      summary calculation,      pivot column,      table name

        'Création de la connection
        Dim conn As New SqlConnection(CONNECTIONSTRING)
        'Création de l'objet de commande
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.Add("@SELECT", Param1)
        cmd.Parameters.Add("@sumfunc", Param2)
        cmd.Parameters.Add("@pivot", Param3)
        cmd.Parameters.Add("@table", Param4)

        'CommandType.StoredProcedure()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandTimeout = 300
        'Création de l'adapteur qui remplirar le datatable
        Dim adapt As New SqlDataAdapter(cmd)
        Dim tmpTable As New DataTable
        'Execution de la requête et FILL du datatable
        'adapt.Fill(tmpTable)
        Try
            adapt.Fill(tmpTable) '<---------------- ICI ERREUR ICI ERREUR ICI ERREUR
        Catch exc As Exception
            Throw exc ' Break point ici!!
        End Try

        Return tmpTable

    End Function





Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try changing the EXEC's in your procedure for PRINT's, this will print each query that is executed and it will help you debug the procedure and find where the problem is.

Author

Commented:
It returned me the following but I still do not see the problem...


SELECT QB2 AS [pivot] INTO ##pivot FROM tblDonnees WHERE 1=2
INSERT INTO ##pivot SELECT DISTINCT QB2 FROM tblDonnees WHERE QB2 Is Not Null
Msg 208, Level 16, State 1, Procedure crosstab, Line 24
Invalid object name '##pivot'.

(1 row(s) affected)


But when I look into my stored procedure in modify mode, line 24 is...


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


Any ideas?
It's giving you that error because the line that "creates" the ##pivot table is not executing, it's being printed.

What you can do, is comment that line so that you can see every single dynamically created SQL instruction, and we can evaluate where it's giving you trouble.

From what I see, the first queries look good, but I'm almost sure that a single quote (') is giving you trouble.

Do that and let me know how it goes.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
It runs without error until I take the following line out of comment...

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

When that line is executed, it returns the following error message...

SELECT QB2 AS [pivot] INTO ##pivot FROM tblDonnees WHERE 1=2
INSERT INTO ##pivot SELECT DISTINCT QB2 FROM tblDonnees WHERE QB2 Is Not Null
Msg 208, Level 16, State 1, Procedure crosstab, Line 24
Invalid object name '##pivot'.

(1 row(s) affected)
Can we see the queries it prints when all of them are set to PRINT and not to EXEC?
Try copying and pasting these queries (without the INTO's) in a sql query window, and see if all of them run successfuly.

Author

Commented:
Again, the first two seemed to work fine, here is the error

SELECT QB2 AS [pivot]  FROM tblDonnees WHERE 1=2
 SELECT DISTINCT QB2 FROM tblDonnees WHERE QB2 Is Not Null
Msg 208, Level 16, State 1, Procedure crosstab, Line 24
Invalid object name '##pivot'.

(1 row(s) affected)

And it returns me this as well...

USE [DB_SAQ]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[crosstab]
            @select = N'SELECT iAnnee, Sum(Pond_1) AS n_p, Count(0) AS n FROM tblDonnees WHERE (((iAnnee)=2011) AND ((QB2) Is Not Null) And ((QB2)<>0) AND ((Succ)= 210071) AND ((iVague)= 1)) GROUP BY iAnnee',
            @sumfunc = N'Pond_1',
            @pivot = N'QB2',
            @table = N'tblDonnees'

SELECT      'Return Value' = @return_value

GO


And the current stored procedure looks like this...

USE [DB_SAQ]
GO
/****** Object:  StoredProcedure [dbo].[crosstab]    Script Date: 08/05/2011 15:59:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[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

PRINT ('SELECT ' + @pivot + ' AS [pivot]  FROM ' + @table + ' WHERE 1=2')
PRINT (' 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 ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

PRINT (@select)
SET ANSI_WARNINGS ON

Top Expert 2010

Commented:
You may want to consider the dynamic crosstab developed by SQL Server MVP and EE superstar mark_wills:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
Slightly modify your procedure like this, and run it in a SQL window. Let us know what it prints, since this last query should be the one causing the problem.

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

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 + ' ')

PRINT (@select)

Open in new window

P.S. Run it with the parameters you're using in your actual code.

Author

Commented:
Thanks, I will try to test this today, I will get back to you.

Author

Commented:
From what I can see you deleted the last 2 lines and replaced them with "PRINT (@select)"

When I ran the procedure in an sql window it returned:


USE [DB_SAQ]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[crosstab]
            @select = N'SELECT iAnnee, Sum(Pond_1) AS n_p, Count(0) AS n FROM tblDonnees WHERE (((iAnnee)=2011) AND ((QB2) Is Not Null) And ((QB2)<>0) AND ((Succ)= 210071) AND ((iVague)= 1)) GROUP BY iAnnee',
            @sumfunc = N'Pond_1',
            @pivot = N'QB2',
            @table = N'tblDonnees'

SELECT      'Return Value' = @return_value

GO

and in my results window I have 1 row with zero as the return value!?!?!???

You altered the stored procedure and ran it with the parameters, right?
What I want you to do is to run the code that I posted, without creating/altering the stored procedure. This should only print the SELECT statement that must be made, it shouldn't print the EXEC of the procedure.

I'm guessing the select statement should start with your select parameter (i.e. SELECT iAnnee, Sum(Pond_1) AS n_p, Count(0) AS n FROM tblDonnees WHERE (((iAnnee)=2011) AND ((QB2) Is Not Null) And ((QB2)<>0) AND ((Succ)= 210071) AND ((iVague)= 1)) GROUP BY iAnnee) and then add some ore stuff from the procedure itself.

What we're trying to do here is to know exactly what is being executed in that last line in your SP, so that we can debug it.

Good luck and let us know.

Author

Commented:
I hope I understood correctly this time...

When I executed the code, I got the following message...

SELECT iAnnee, Sum(Pond_1) AS n_p, Count(0) AS n , '1' =  CASE QB2 WHEN 1 THEN Pond_ END), '2' =  CASE QB2 WHEN 2 THEN Pond_ END) FROM tblDonnees WHERE (((iAnnee)=2011) AND ((QB2) Is Not Null) And ((QB2)<>0) AND ((Succ)= 210071) AND ((iVague)= 1)) GROUP BY iAnnee

(1 row(s) affected)

There's your error!

See the closed parentheses that have no opening.

SELECT iAnnee, Sum(Pond_1) AS n_p, Count(0) AS n , '1' =  CASE QB2 WHEN 1 THEN Pond_ END), '2' =  CASE QB2 WHEN 2 THEN Pond_ END) FROM tblDonnees WHERE (((iAnnee)=2011) AND ((QB2) Is Not Null) And ((QB2)<>0) AND ((Succ)= 210071) AND ((iVague)= 1)) GROUP BY iAnnee


You can copy that query and try to run it in a sql query window, it will throw the error you were looking for...
Hope that helps you solve your issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial