CREATE PROCEDURE dbo.ap_get_sales_list 

@YardID varchar(4), 
@Year varchar(4), 
@Make varchar(30), 
@Model varchar(30), 
@TitleType varchar(20), 
@DamageType varchar(40), 
@Dates varchar(20)

as 

Begin 

set nocount on 

--First grab all other columns needed to populate Datagrid
Declare cursid Cursor for 
	select item_Id from aims_salelist_columns
	where display_item=1
	order by display_order

Declare @itemID int,
	@tempValue varchar(200), 
	@curColumn varchar(100), 
	@curName varchar(100), 
	@finalColumns varchar(2000), 
	@finalQuery varchar(8000)

set @finalColumns=''

Open Cursid 
fetch next from cursid into @itemID
while (@@fetch_status<>-1) 
Begin 
	select @curColumn=db_table_name, @curName=display_name 
	from aims_salelist_columns
	where @itemID=item_id

	set @tempValue = @curColumn + ' as [' + @curName + ']'
	set @finalcolumns = @finalcolumns + @tempValue + ', '

set @tempValue=''
set @curColumn=''
set @curName=''
fetch next from cursid into @itemID
End
		
deallocate Cursid 
--print 'Final columns = ' + @finalcolumns

set @finalQuery = 'select stockID, imageID, Year, Make, Model, PrimaryDamage, ' + @finalcolumns + 'ISNULL(NewTitleType, OrigTitleType) as TitleType ' 
set @finalQuery = @finalQuery + 'from aims.dbo.websalelist a join aims.dbo.sale b on a.SaleID=b.SaleID where year like ''' +  @year + ''' and make like ''' + @make + ''' and model like ''' + @model + ''' and ISNULL(NewTitleType, OrigTitleType) like ''' + @titletype + '''' 
set @finalQuery = @finalQuery + 'and (primarydamage like ''' + @DamageType + ''' or secondaryDamage like ''' + @DamageType + ''') and a.SaleID like ''' + @Dates + ''' and b.yardID like ''' + @YardID + ''' and b.saleDate >=getdate()'

--print @finalquery
exec(@finalquery)

End
GO

quote:

---

Originally posted by nr

Actually why have a second temp table. You can incorporate this in the dynamic sql - maybe then you won't need to put the output into a temp table and it can just be a select.

---

SELECT * INTO #tempTable
FROM myTable

Declare @finalColumns varchar(2000), 
	@finalQuery varchar(8000)

set @finalColumns=''

-- Builds your column list
select final_columns = db_table_name + ' as ['+display_name+'], '
from aims_salelist_columns
where display_item=1
order by display_order