Solved

Add alias fields to a proc

Posted on 2014-11-24
10
152 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 33

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 33

Author Closing Comment

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

Mike
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

770 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