Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Add alias fields to a proc

Posted on 2014-11-24
10
Medium Priority
?
164 Views
Last Modified: 2014-11-24
The following proc returns FieldNames in a table:
ALTER PROCEDURE  [dbo].[spGetFieldNames]
@TableName varchar(100)

As
Begin
 
Declare @sql varchar(max);

 Set @sql= 'select COLUMN_NAME as FieldName
from information_schema.columns 
where table_name = ''' + @TableName + 
''' order by ORDINAL_POSITION'
exec (@sql)

end

Open in new window


Question: How can I add the followings alias fields to this proce:
- Field named No to number the records as 1, 2, 3, etc.
- Field name UserName with value 'Default'
- Field name include with value 'true'
0
Comment
Question by:Mike Eghtebas
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 800 total points
ID: 40462570
Add this in between lines 9 and 10 (note it starts with a comma):

, row_NUMBER() over(order by COLUMN_NAME) as No, ''Default'' as UserName, ''True'' as Include

Open in new window

0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40462579
This should do it:
ALTER PROCEDURE  [dbo].[spGetFieldNames]
@TableName varchar(100)

As
Begin
 
Declare @sql varchar(max);

 Set @sql= 'select row_number() over(order by table_name, column_name) No, 
cast(''Default'' as varchar(128)) UserName,
cast(''true'' as varchar(50)) [Include],
table_name,
COLUMN_NAME as FieldName
from information_schema.columns 
where table_name = ''' + @TableName + 
''' order by ORDINAL_POSITION'
exec (@sql)

end

Open in new window

0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 800 total points
ID: 40462584
Set @sql= 'select ordinal_position no, column_name as FieldName, table_schema as Username, 1 as include
from information_schema.columns
where table_name = ''' + @TableName +
''' order by ORDINAL_POSITION'

or

 Set @sql= 'select ordinal_position no, column_name as FieldName, table_schema as Username, ''true'' as include
from information_schema.columns
where table_name = ''' + @TableName +
''' order by ORDINAL_POSITION'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 40462585
Give this a whirl... (Assumes you wanted No to be ordered by the way it appears in the table)
Declare @sql varchar(1000), @TableName varchar(100) = 'your table name goes here'

Set @sql= 'select ROW_NUMBER() OVER (ORDER BY (SELECT (0))) as No, 
   COLUMN_NAME as FieldName, 
   ''Default'' as UserName, 
   ''True'' as include
from information_schema.columns 
where table_name = ''' + @TableName + 
''' order by ORDINAL_POSITION'
exec (@sql)

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40462586
<snicker>
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 40462587
no need for any row_number() or any other thing here, ORDINAL_POSITION is what you all need i guess...
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 40462591
also, not sure about username! where is that coming from? a fixed column? if yes, then

 Set @sql= 'select ordinal_position no, column_name as FieldName, ''Default'' as Username, ''true'' as include
from information_schema.columns
where table_name = ''' + @TableName +
''' order by ORDINAL_POSITION'
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40462635
Thank you all,

The car-race flag of EE says the fastest care (gun) wins. So in order of entry, I tied:

Phillip,
It works but messes up the order of display I was hoped for.  <-- accepted answer w/ assistance


Kyle,
Similar to the answer from Phillip.

HainKurt,
It works keeping the order as I hoped for.   <-- assistance

Regards,

Mike
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 40462641
Don't use INFORMATION_SCHEMA views in SQL Server.  They are much more overhead; you can verify that by running both versions after this statement.
SET STATISTICS IO ON
and/or looking at the query plans.
They are also missing a lot of info that SQL's metadata views contain.

Also, for performance reasons, don't use varchar(max) unless  you really need it, or really might need it.

Finally, the approach below allows a schema name to be included in the table name, with no changes to the code.


Declare @sql varchar(8000);

Set @sql= 'select row_number() over(order by column_id) as No,
name AS FieldName,
''Default'' AS UserName,
''True'' AS Include
from sys.columns
where object_id = OBJECT_ID(''' + @TableName + ''')
order by column_id '
exec (@sql)
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40462659
But when it comes to ScottPletcher, exception applies.

Mike
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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

916 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