Solved

PROC to return table field names

Posted on 2014-11-23
9
227 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
  • 4
  • 4
9 Comments
 
LVL 24

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 33

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 33

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 33

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 33

Author Closing Comment

by:Mike Eghtebas
ID: 40461427
Thank you.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now