Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

SQL Server dynamically return specific columns from a table

Hello.  I've got a table [##UDF_HCLOT_prep] that contains up to 40 columns.  I'm trying to return only those columns that don't begin with 'Lot-UDF%'.  

I have the following code as shown below, but when I execute it all I get back is "Invalid object name ##LotUDFs".

Any help is appreciated.
declare @sql varchar(1000)
SELECT @sql=@sql + Column_name + ','
from INFORMATION_SCHEMA.Columns
where table_name = '##UDF_HCLOT_prep' and Column_name not like '[Lot-UDF]%'

select @sql = left(@sql, len(@sql)-1)
select @sql = 'select ' + @sql + ' Into ##LotUDFs From ##UDF_HCLOT_prep'
exec (@sql)

Select * From ##LotUDFs

Open in new window

0
jobprojn
Asked:
jobprojn
  • 3
  • 2
1 Solution
 
BrandonGalderisiCommented:
That is because when you execute SQL using exec(@SQL), the temp table goes out of scope once the execution is complete.  Try this...


declare @sql nvarchar(max)
set @SQL = ''
SELECT @sql=@sql + '[' + Column_name + '],'
from INFORMATION_SCHEMA.Columns
where table_name = '##UDF_HCLOT_prep' and Column_name not like '[Lot-UDF]%'

select @sql = left(@sql, len(@sql)-1)
select @sql = 'select ' + @sql + ' From ##UDF_HCLOT_prep'
exec sp_executesql @SQL


0
 
cyberkiwiCommented:
declare @sql varchar(1000)
SELECT @sql=@sql + c.name + ','
from tempdb.sys.objects o
inner join tempdb.sys.columns c on c.object_id=o.object_id
where o.name = '##UDF_HCLOT_prep' and c.name not like '[Lot-UDF]%'

select @sql = left(@sql, len(@sql)-1)
select @sql = 'select ' + @sql + ' Into ##LotUDFs From ##UDF_HCLOT_prep'
exec (@sql)

Select * From ##LotUDFs
0
 
jobprojnAuthor Commented:
Brandon, thanks for quick reply.  Because I'm using a global temp table "##" the table should remain in scope even after execution.  Nonetheless, I tried your code and get:

Msg 536, Level 16, State 5, Line 7
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'From'.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jobprojnAuthor Commented:
Cyberwiki, thanks for quick reply.  Using your code I get the following:

Msg 208, Level 16, State 1, Line 11
Invalid object name '##LotUDFs'.
0
 
BrandonGalderisiCommented:
Global temp tables will remain in scope until the last process (SPID) accessing it exits.  Since ##LotUDFs is only accessed by the exec(), it goes out of scope immediately upon completion.

Mine failed because I wasn't accessing tempdb.

Try:

declare @sql nvarchar(max)

SELECT @sql=isnull(@sql + ',' ,'') + '[' + Column_name + ']'
from tempdb.INFORMATION_SCHEMA.Columns
where table_name = '##UDF_HCLOT_prep' and Column_name not like '[Lot-UDF]%'


select @sql = 'select ' + @sql + ' From med##UDF_HCLOT_prepium'
--exec sp_executesql @SQL
PRINT @SQL


0
 
jobprojnAuthor Commented:
Worked, thanks much.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now