Solved

Add alias fields to a proc

Posted on 2014-11-24
10
153 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 200 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 40

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 51

Assisted Solution

by:HainKurt
HainKurt earned 200 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 65

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 65

Expert Comment

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

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 51

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 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

829 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