Solved

PROC to return table field names

Posted on 2014-11-23
9
236 Views
Last Modified: 2014-11-23
Table1        <-- starting with a table like this
===============================
Field1   Field2    Field3       etc.
-------    --------      ---------
x             x              x
x             x              x
x             x              x

The date in this table to be ignored. The return expected is:

FieldNames
============
Field1
Field2
Field3
etc.


Question: How to write a Proc or function to return the field names in Table1 as shown above?
0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 250 total points
ID: 40461367
Use INFORMATION_SCHEMA.COLUMNS system view for this:
@sql = 'select COLUMN_NAME 
from information_schema.columns 
where table name = ''' + @YourTable + 
''' order by ORDINAL_POSITION'
EXEC @sql

Open in new window

0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40461376
wonderful. Is it a good idea to make a function or a proc, performance-wise?
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40461382
Depends on what you plan to do with it.
Functions can be used in-line in select statements, procedures cannot. But procedures perform better.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40461386
performance-wise?
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40461394
Like I said, procedures perform better. They're faster than functions.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40461407
Koen Van Wielink,
Like I said, procedures perform better.
Sorry, my bad.

chaau,
I ran this PROC, I got the following error::
Msg 2812, Level 16, State 62, Procedure spGetFieldNames, Line 14
Could not find stored procedure 'select COLUMN_NAME
from information_schema.columns
where table name = 'Search' order by ORDINAL_POSITION'.



ALTER PROCEDURE  [dbo].[spGetFieldNames]
@TableName varchar(100)

As
Begin
 
Declare @sql varchar(max);

-- I tried Select also
 Set @sql= 'select COLUMN_NAME 
from information_schema.columns 
where table name = ''' + @TableName + 
''' order by ORDINAL_POSITION'
exec @sql

end

Open in new window

0
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 250 total points
ID: 40461416
There's an underscore missing in the where clause:

where table_name = ''' + @TableName + 

Open in new window


When working with dynamic SQL, try to print your generated statements before executing them. If you get an error, you can copy/paste the printed statement to see where things go wrong.

declare @TableName varchar(100)

select @TableName = 'WorkOrder'
 
Declare @sql varchar(max);

-- I tried Select also
 Set @sql= 'select COLUMN_NAME 
from information_schema.columns 
where table name = ''' + @TableName + 
''' order by ORDINAL_POSITION'
--exec @sql
print @sql

Open in new window

0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40461418
Oh, and you need to place () around the @sql variable in the exec statement:

exec (@sql)

Open in new window

0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40461427
Thank you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question