Challenging Stored Procedure - HELP PLEASE

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Challenging Stored Procedure - HELP PLEASE

Author

Topic

THEITGUY
Starting Member

4 Posts

Posted - 2004-08-26 : 18:47:46

I have this table:

Table Name: CompInfo
Field1: Model0
Field2: Location0

I am trying to write a dynamic crosstab query that will count the computers per location. I have this stored procedure that will build the select statement dynamically:

CREATE PROC sp_CrossTab(
@table AS sysname, -- Table to crosstab
@onrows AS nvarchar(128), -- Grouping key values (on rows)
@onrowsalias AS sysname = NULL, -- Alias for grouping column
@oncols AS nvarchar(128), -- Destination columns (on columns)
@sumcol AS sysname = NULL -- Data cells
)

AS

DECLARE
@sql AS varchar(8000),
@NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

SET @sql =
'SELECT' + @NEWLINE +
' ' + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
ELSE ''
END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
'FROM ' + @table

EXEC (@keyssql)

DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN ''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS ' + '[' + @key + ']'

SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END

SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows + @NEWLINE +
'WITH CUBE'
PRINT @sql + @NEWLINE --Used for debug
EXEC (@sql)
GO

To run the SP I use this:
EXEC sp_CrossTab
@table = 'CompInfo',
@onrows = 'Location0',
@onrowsalias = 'Location',
@oncols = 'Model0'

When I run this in the SQL analyzer I get the following output The first part is a print out of the dynamic query that the sp built. But the query craps out and I get a couple of errors.

(42 row(s) affected)

SELECT
Location0 AS Location,
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN '26453EU' THEN 1
ELSE 0
END) AS [26453EU],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN '6578RBU' THEN 1
ELSE 0
END) AS [6578RBU],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN '6792R6U' THEN 1
ELSE 0
END) AS [6792R6U],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN '831941U' THEN 1
ELSE 0
END) AS [831941U],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN '864421U' THEN 1
ELSE 0
END) AS [864421U],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'Compaq Evo D510 e-pc' THEN 1
ELSE 0
END) AS [Compaq Evo D510 e-pc],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'DSDT' THEN 1
ELSE 0
END) AS [DSDT],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'Evo D510 CMT' THEN 1
ELSE 0
END) AS [Evo D510 CMT],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'HP d530 CMT(DC577AV)' THEN 1
ELSE 0
END) AS [HP d530 CMT(DC577AV)],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'HP NetServer' THEN 1
ELSE 0
END) AS [HP NetServer],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'HP Server' THEN 1
ELSE 0
END) AS [HP Server],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'Latitude C610' THEN 1
ELSE 0
END) AS [Latitude C610],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'Latitude C840' THEN 1
ELSE 0
END) AS [Latitude C840],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'Latitude CPi A366XT' THEN 1
ELSE 0
END) AS [Latitude CPi A366XT],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'Latitude CPi A400XT' THEN 1
ELSE 0
END) AS [Latitude CPi A400XT],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'Latitude CPx H500GT' THEN 1
ELSE 0
END) AS [Latitude CPx H500GT],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'Latitude CPx J650GT' THEN 1
ELSE 0
END) AS [Latitude CPx J650GT],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'NetServer LH 3' THEN 1
ELSE 0
END) AS [NetServer LH 3],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX1 350Mbr+' THEN 1
ELSE 0
END) AS [OptiPlex GX1 350Mbr+],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX1 350MTbr+' THEN 1
ELSE 0
END) AS [OptiPlex GX1 350MTbr+],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX1 400MTbr+' THEN 1
ELSE 0
END) AS [OptiPlex GX1 400MTbr+],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX1 450M' THEN 1
ELSE 0
END) AS [OptiPlex GX1 450M],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX1 500M' THEN 1
ELSE 0
END) AS [OptiPlex GX1 500M],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX1 500Mbr' THEN 1
ELSE 0
END) AS [OptiPlex GX1 500Mbr],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX1 500MTbr' THEN 1
ELSE 0
END) AS [OptiPlex GX1 500MTbr],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX100' THEN 1
ELSE 0
END) AS [OptiPlex GX100],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX110' THEN 1
ELSE 0
END) AS [OptiPlex GX110],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX150' THEN 1
ELSE 0
END) AS [OptiPlex GX150],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX240' THEN 1
ELSE 0
END) AS [OptiPlex GX240],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX260' THEN 1
ELSE 0
END) AS [OptiPlex GX260],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX270' THEN 1
ELSE 0
END) AS [OptiPlex GX270],
SUM(CASE CAST(Model0 AS nvarchar(100))
WHEN 'OptiPlex GX4

Server: Msg 105, Level 15, State 1, Line 128
Unclosed quotation mark before the character string 'OptiPlex GX4'.
Server: Msg 170, Level 15, State 1, Line 128
Line 128: Incorrect syntax near 'OptiPlex GX4'.

I've been studying this for days and I can't seem to find the problem. Can any of you see a problem with this? I am not an advanced DBA or SQL guru so any help would be greatly appreciated. Thanks in advance!

gpl
Posting Yak Master

195 Posts

Posted - 2004-08-26 : 18:56:20

Check out this article, Ive used it and its excellent !!
whoops, forgot the link -> http://www.sqlteam.com/item.asp?ItemID=2955

I wouldnt be at all surprised if you had filled up all of your 8K string, its easy to do !

Graham

THEITGUY
Starting Member

4 Posts

Posted - 2004-08-26 : 19:13:35

I Think you're right about having exceeded the 8k string. I just tried it on a smaller database and it works. So now how do I get around this? I'll take a look at the link you posted. Thanks Graham!

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-27 : 13:42:24

Here is a crosstab procedure that claims to support 762 columns.

http://www.developersdex.com/gurus/code/826.asp

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

THEITGUY
Starting Member

4 Posts

Posted - 2004-08-27 : 19:23:11

Well I did it. The probelm was that I was exceeding the 8k limit - so I created another variable and just concatenated on execute. Here's the full code, but don't laugh cause I'm not a DBA! I'm sure there are cleaner ways to do it. :)

CREATE PROC sp_CrossTab(
@table AS sysname, -- Table to crosstab
@onrows AS nvarchar(128), -- Grouping key values (on rows)
@onrowsalias AS sysname = NULL, -- Alias for grouping column
@oncols AS nvarchar(128), -- Destination columns (on columns)
@sumcol AS sysname = NULL -- Data cells
)

AS

DECLARE
@sql1 AS varchar(8000),
@sql2 AS varchar(8000),
@sql3 AS varchar(8000),
@sql4 AS varchar(8000),
@count as int,
@NEWLINE AS char(1)

SET @count = 1
SET @NEWLINE = CHAR(10)
SET @sql2 = ''
SET @sql3 = ''

SET @sql1 =
'SELECT' + @NEWLINE +
' ' + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
ELSE ''
END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
'FROM ' + @table

EXEC (@keyssql)

DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL and @count <= 31
BEGIN

SET @sql1 = @sql1 + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN ''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS ' + '[' + @key + ']'

SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
set @count = @count + 1

END

SET @count = 32

WHILE @key IS NOT NULL and @count > 31
BEGIN

SET @sql2 = @sql2 + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN ''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS ' + '[' + @key + ']'

SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
set @count = @count + 1

END

set @sql3 = ',' + @NEWLINE + 'COUNT(' + @oncols + ') AS TOTAL'

SET @sql4 = @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'WITH CUBE'

EXEC (@sql1 + @sql2 + @sql3 + @sql4)
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources