Add alias fields to a proc

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'
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperCommented:
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
HainKurtSr. System AnalystCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<snicker>
0
HainKurtSr. System AnalystCommented:
no need for any row_number() or any other thing here, ORDINAL_POSITION is what you all need i guess...
0
HainKurtSr. System AnalystCommented:
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
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
Scott PletcherSenior DBACommented:
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
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
But when it comes to ScottPletcher, exception applies.

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.