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: 166
  • Last Modified:

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'
0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 3
  • 2
  • 2
  • +3
3 Solutions
 
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
 
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
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.

 
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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