8k or bust

Question

Post reply

8k or bust

MBHblows

SSCommitted

Points: 1554
More actions
October 4, 2005 at 2:38 pm

#166295

OKay, I have a stored proc that generates a query into a local variable. my problem is that in some instances the query that is created exceed 8k and ends up truncated. What are some suggestions to deal with an issue like this?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Split it into 3-4 parts.

then exec (@Select + @From + @Where + @OrderBy)

MBHblows SSCommitted Points: 1554 More actions · Answer 2

Okay, I'm not sure where I can do that in this instance. Take a look.

Here is the proc that creates the query

DECLARE @CustomTableColumnID AS UNIQUEIDENTIFIER

DECLARE @ColumnName AS VARCHAR(255)

DECLARE @FinalSQL AS VARCHAR(8000)

DECLARE @SubQueryText AS VARCHAR(255)

declare @CustomerID AS UNIQUEIDENTIFIER

declare @CustomTableID AS UNIQUEIDENTIFIER

DECLARE @FinalSQL2 AS VARCHAR(8000)

set @CustomerID = '4b13ae9e-7eab-45b0-a110-2052e39fc763'

set @CustomTableID = '9ca2a21f-d047-4247-b1cc-e089e2cc5f39'

--These two variables are set with initial values to concatinate together to build a TSQL command to execute.

SET @SubQueryText='(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='''

SET @FinalSQL='SELECT a.CustomTableRowID AS RowID '

--Initiaize a cursor to hold the records needed to build our dynamic TSQL statement.

DECLARE Columns_cursor CURSOR FAST_FORWARD FOR

SELECT CustomTableColumnID, ColumnName FROM tblCustomTableColumns WHERE CustomerID=@CustomerID AND CustomTableID=@CustomTableID ORDER BY OrderNum

OPEN Columns_cursor

-- Perform the first fetch and store the values in variables.

-- Note: The variables are in the same order as the columns

-- in the SELECT statement.

FETCH FROM Columns_cursor

INTO @CustomTableColumnID, @ColumnName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @FinalSQL = @FinalSQL + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

FETCH NEXT FROM Columns_cursor

INTO @CustomTableColumnID, @ColumnName

END

SET @FinalSQL = @FinalSQL + ' FROM tblCustomTableRows a WHERE a.CustomerID=''' + CONVERT(VARCHAR(40), @CustomerID) + ''' AND a.CustomTableID=''' + CONVERT(VARCHAR(40), @CustomTableID) + ''''

print @finalsql

CLOSE Columns_cursor

DEALLOCATE Columns_cursor

EXEC(@FinalSQL)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

And here is the output that it creates up to 8k...

SELECT a.CustomTableRowID AS RowID ,(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='1FDB0737-08AE-4B4C-B5C4-87CFF42705DF') AS [InternalClientID],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='C118DFA7-FB88-4FE4-96AD-AA42C3A1D5FC') AS [Rule50],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='EF931947-E49E-40C4-AC61-600B0EAEB1A2') AS [Rule105],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='2FFA47DF-3568-4A13-8197-5D0BD1CCA212') AS [Rule106],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='A58F6003-A47B-4D6B-93F1-F3E61482CB08') AS [Rule107],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='6FF799E8-1C1F-4630-AA44-0472D0A5796B') AS [Rule108],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='529558F3-8E54-4DF3-839F-4A1FA2D0FB8A') AS [Rule109],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='927A2710-815A-4E67-A599-9AC83077A4D8') AS [Rule110],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='D9195687-1877-463C-9C3D-26AD3198A858') AS [Rule111],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='8F6EB3F9-F114-429C-9E8C-1C1FECD3F410') AS [Rule112],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='12BD8002-8D7B-47EC-B286-8F637C4ADF17') AS [Rule113],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='ECAD9248-524C-4F8D-9C69-49CA224C7E58') AS [Rule114],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='40A393AF-7058-4449-A2E8-6D5350FC3F93') AS [Rule115],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='949D8F98-6922-4B6F-AA7F-0569D57E5264') AS [Rule116],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='33C86163-5BEB-44EE-9F03-9A794B6E2D05') AS [Rule117],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='F06F4318-1872-41AA-9A4B-A05D9E3FB652') AS [Rule121],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='2AAC8147-9D6F-4134-BF09-9690CBDB5C60') AS [Rule122],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='3D2019E7-A631-4AFA-8026-CB94174BB1CD') AS [Rule124],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='6B4D3067-FC16-41FB-B27B-823A11ACB671') AS [Rule125],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='161D8CC3-1896-4910-82AD-5ABC978600FE') AS [Rule126],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='A27187FD-1F11-4EA0-80ED-BF617EC44FC5') AS [Rule127],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='47C6AFC6-4C19-4804-928A-684D32E6DC54') AS [Rule129],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='80946DAC-A30C-4691-90F4-2C96CC452E9C') AS [Rule130],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='20C68C9F-E944-4F54-A21B-BEF11E3FA95C') AS [Rule131],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='66A9A28C-3E97-4D53-A26B-FB3978220636') AS [Rule133],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='7523E6B7-B9DF-4133-B4AA-7BB21C341285') AS [Rule134],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='D2DC9C3B-0653-4BCE-B3D1-A6B07521B859') AS [Rule135],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='D8B531B6-B9BB-4534-A7ED-A004EBA97DE6') AS [Rule136],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='2E508E62-61DC-45BE-87FD-CD6DB725F878') AS [Rule137],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='0DB3459D-D4A2-4187-96D6-EE31688CAEA7') AS [Rule142],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='7904077E-3DAF-4C55-A686-4968D57008C6') AS [Rule143],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='FF004C2E-2105-4C21-A0B9-A4E097841EDC') AS [Rule160],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='8A80151A-C809-4EEE-BED1-23F7D6D1CBB1') AS [Rule161],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='7BA29C0C-6C4D-430C-B9EF-8734DE5CEE86') AS [Rule162],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='227E5BA8-39F1-4ED4-B523-32BF6B135290') AS [Rule165],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='1376A366-2010-4857-9914-D002DB7158BE') AS [Rule171],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='5413F835-13AD-4B0C-9E07-486EAE140F2D') AS [Rule172],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='10F7280C-4F07-456A-8E23-5854A0B53190') AS [Rule173],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='8528A7C2-88A7-462B-A8FB-6FC9C9CF6FF6') AS [Rule175],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='B8929567-9ED5-46FC-9E66-7A8826792D65') AS [Rule180],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='69EB93D4-C638-4849-8E4B-995D53015977') AS [Rule181],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='1B273B71-3F45-4205-924C-417F7F977EC7') AS [Rule182],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='2E5045AC-18CF-4A96-A3E1-DC996620C968') AS [Rule185],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='0D932181-1273-47B2-835B-7635B52DDC8E') AS [Rule186],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='8AE222FA-73FE-4B6C-8C85-2AC59AEB0905') AS [Rule187],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='0903186D-D985-4B86-B9BA-A3DE79EC7BC5') AS [Rule188],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='76618882-7F02-48B0-B794-06AB144B427A')

There is supposed to be much more to this query and im not sure how to capture it.

noeld SSC Guru Points: 96590 More actions · Answer 3

I am almost certaint that there is a better way to do that without following this route but that is not the point of my reply. This is a quick fix for your statement

You declare lets say four variables at the top

Declare @SQL1 varchar(8000), @SQL2 varchar(8000), @SQL3 varchar(8000), @SQL4 varchar(8000) , @data varchar(8000)

select @SQL1 ='', @SQL2 ='', @SQL3 ='', @SQL4 ='', @data =''

--- inside the LOOP you just write this

WHILE @@FETCH_STATUS = 0

BEGIN

set @data = ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

if Len( @data ) + len(@SQL1) < 8000

begin

SET @SQL1 = @SQL1 + @data

end

else

if Len( @data ) + len(@SQL2) < 8000

begin

SET @SQL2 = @SQL2 + @data

end

else

if Len( @data ) + len(@SQL3) < 8000

begin

SET @SQL3 = @SQL3 + @data

end

else

if Len( @data ) + len(@SQL4) < 8000

begin

SET @SQL4 = @SQL4 + @data

end

FETCH NEXT FROM Columns_cursor

INTO @CustomTableColumnID, @ColumnName

END

-- and at the end

exec ( @SQL1 + @SQL2 + @SQL3 + @SQL4)

* Noel

David le Quesne SSCertifiable Points: 5229 More actions · Answer 4

Hi

I was wondering if you could build a SQL statement that used a JOIN and a searched CASE to generate the different columns instead of all the inline SELECTs. It might make your generated SQL statement more compact.

Something like this saves about 55 characters per column...

SELECT...

CASE WHEN b.CustomTableColumnID='C118DFA7-FB88-4FE4-96AD-AA42C3A1D5FC' THEN ColumnValue ELSE NULL END AS [Rule50],

CASE WHEN b.CustomTableColumnID='EF931947-E49E-40C4-AC61-600B0EAEB1A2' THEN ColumnValue ELSE NULL END AS [Rule105],

...etc...

FROM tblCustomTableRows a

JOIN tblCustomTableRowColumns b

ON (b.CustomTableRowID=a.CustomTableRowID)

David

If it ain't broke, don't fix it...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

October 5, 2005 at 7:17 am

#595112

And it's gonna run much faster .

MBHblows SSCommitted Points: 1554 More actions · Answer 6

I;m sorry for my ignorence. Both ideas seem great but, I'm having trouble applying them in my current statement. Where in the current proc can I apply these methods and actually make them work.

Casey Pettingill-185031 SSC Journeyman Points: 97 More actions · Answer 7

I am no expert at pivot tables and such, but if I understand the above posts, a hybrid approach would be something like this (not guaranteed to work since I haven't tested it )

DECLARE @CustomTableColumnID AS UNIQUEIDENTIFIER

DECLARE @ColumnName AS VARCHAR(255)

DECLARE @SelectSQL1 AS VARCHAR(8000)

DECLARE @SelectSQL2 AS VARCHAR(8000)

DECLARE @SelectSQL3 AS VARCHAR(8000)

DECLARE @SelectSQL4 AS VARCHAR(8000)

DECLARE @Data AS VARCHAR(8000)

DECLARE @FromSQL AS VARCHAR(8000)

DECLARE @WhereSQL AS VARCHAR(8000)

DECLARE @SubQueryText AS VARCHAR(255)

declare @CustomerID AS UNIQUEIDENTIFIER

declare @CustomTableID AS UNIQUEIDENTIFIER

DECLARE @FinalSQL2 AS VARCHAR(8000)

select @SelectSQL1 ='', @SelectSQL2 ='', @SelectSQL3 ='', @SelectSQL4 ='', @Data ='', @FromSQL='', @WhereSQL=''

set @CustomerID = '4b13ae9e-7eab-45b0-a110-2052e39fc763'

set @CustomTableID = '9ca2a21f-d047-4247-b1cc-e089e2cc5f39'

SET @SubQueryText='CASE WHEN b.CustomTableColumnID=''<CUSTOM_TABLE_COLUMN_ID>'' THEN b.ColumnValue ELSE NULL END AS [<COLUMN_NAME>]'

SET @SelectSQL1 ='SELECT a.CustomTableRowID AS RowID '

--Initiaize a cursor to hold the records needed to build our dynamic TSQL statement.

DECLARE Columns_cursor CURSOR FAST_FORWARD FOR

SELECT CustomTableColumnID, ColumnName

FROM tblCustomTableColumns

WHERE CustomerID=@CustomerID

AND CustomTableID=@CustomTableID

ORDER BY OrderNum

OPEN Columns_cursor

-- Perform the first fetch and store the values in variables.

-- Note: The variables are in the same order as the columns

-- in the SELECT statement.

FETCH FROM Columns_cursor

INTO @CustomTableColumnID, @ColumnName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @data = ',' + REPLACE(REPLACE(@SubQueryText, '<CUSTOM_TABLE_COLUMN_ID>', CONVERT(VARCHAR(40), @CustomTableColumnID)), '<COLUMN_NAME>', @ColumnName)

if Len( @data ) + len(@SelectSQL1) < 8000

begin

SET @SelectSQL1 = @SelectSQL1 + @data

end

else

if Len( @data ) + len(@SelectSQL2) < 8000

begin

SET @SelectSQL2 = @SelectSQL2 + @data

end

else

if Len( @data ) + len(@SelectSQL3) < 8000

begin

SET @SelectSQL3 = @SelectSQL3 + @data

end

else

if Len( @data ) + len(@SelectSQL4) < 8000

begin

SET @SelectSQL4 = @SelectSQL4 + @data

end

FETCH NEXT FROM Columns_cursor

INTO @CustomTableColumnID, @ColumnName

END

SET @FromSQL = ' FROM tblCustomTableRows a INNER JOIN tblCustomTableRowColumns b ON a.CustomTableRowId = b.CustomTableRowId ';

SET @WhereSQL = ' WHERE a.CustomerID=''' + CONVERT(VARCHAR(40), @CustomerID) + ''' AND a.CustomTableID=''' + CONVERT(VARCHAR(40), @CustomTableID) + ''''

CLOSE Columns_cursor

DEALLOCATE Columns_cursor

EXEC(@SelectSQL1 + @SelectSQL2 + @SelectSQL3 + @SelectSQL4 + @FromSQL + @WhereSQL)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

MBHblows SSCommitted Points: 1554 More actions · Answer 8

MBHblows

SSCommitted

Points: 1554

October 5, 2005 at 12:09 pm

#595225

Out standing. Thanks everone for your help.

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 9

I think you may need further modification to get one row per CustomTableRowID.

The subqueries should be something like "MAX(CASE ... END) as [<COLUMN_NAME>]", and add GROUP BY a.CustomTableRowID at the end.

MBHblows SSCommitted Points: 1554 More actions · Answer 10

Yes that is another problem that I need to resolve. Please elaborate. I'm not quite sure what you mean.

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 11

Starting with the script Casey posted, change the line

SET @SubQueryText='CASE ... END AS [<COLUMN_NAME>]'

to

SET @SubQueryText='MAX(CASE ... END) AS [<COLUMN_NAME>]'

then modify the final EXEC to

EXEC(... + @WhereSQL + ' GROUP BY a.CustomTableRowID')