asked on

Sorting a dynamic query

Hi all.

This one crazy code... It it possible to sort the content of a dynamic query..
I need to output exactacly the result as selected in the where column..

SELECT
						@DynamicQuery = ISNULL(@DynamicQuery+' UNION ','') 
						+ 'SELECT  CONVERT(varchar(max),'+COLUMN_NAME+') as varColumnName, 
						'+@VarCusHeaderID+' as fk_Header_ID,  
						'+@varProviderID+' as fk_ProviderID, 
						'+@varCurIndex+' as PositionIndex, 
						'+@varIsStatus+' as IsStatus		    
						FROM '+@TransactionTable+
						' y' +ISNULL('  WHERE '+@TableWhere,'')
					FROM INFORMATION_SCHEMA.COLUMNS
					WHERE (table_name = @TransactionTable
					AND COLUMN_NAME IN (
									'Text1',
									'Text2',
									'Text3',
									'Text4',
									'Text5',
									'Text6',
									'Text7',
									'Text8',
									'Text9',
									'Text10'
									)
					
								)

Open in new window

QuinnDex

simple solution would be to add an extra id column incrementing by 1 this would order them by default in the order processed

Harish Varghese

And here is how:

Declare another int variable @cnt and replace first 3 lines with below:

SELECT	@cnt = isnull(@cnt, 0) + 1,
@DynamicQuery = ISNULL(@DynamicQuery+' UNION  ','') 
+ 'SELECT ' + CONVERT(varchar, @cnt) + ' SlNo, CONVERT(varchar(max),'+COLUMN_NAME+') as varColumnName,'

Open in new window

-Harish

ZURINET

ASKER

Hi Harish

Thanks for your feedback..
I am having an error that says..

Conversion failed when converting the varchar value ' y' to data type int.

Harish Varghese

Please post your complete query

ASKER CERTIFIED SOLUTION