Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am creating a fully dynamic application but have ran into a bit of a hiccup. There are multiple 'undefined' fields that can be defined by the users. The only problem is redisplaying them. Currently they are showing as [UDF_0] to [UDF_7], i.e. :

SELECT [TABLE].[UDF_0],
       [TABLE].[UDF_1],
       [TABLE].[UDF_2],
       [TABLE].[UDF_3],
       [TABLE].[UDF_4],
       [TABLE].[UDF_5],
       [TABLE].[UDF_6],
       [TABLE].[UDF_7]
FROM [TABLE]

Would obviously display as:

UDF_0 || UDF_1 || etc...

What I would like to be able to do is display them something to this effect, but I can't for the life of me figure it out.

EXECUTE PROCEDURE [dbo].[GetProjectInfo] @Project varchar(100)

AS
BEGIN

SELECT [TABLE].[UDF_0] AS (SELECT [TBL_PROJECT_DESC].[UDF_0]
                           FROM [TBL_PROJECT_DESC]
                           WHERE [TBL_PROJECT_DESC].[PROJECT_NAME]=@Project),
       --etc....
FROM [TABLE]

Desired display would be :

Field Name 0 || Field Name 1 || etc...
share|improve this question
3  
Why do you care what are the column names in the SQL result? Can't you display them any way you want in your application? –  svick Apr 23 '13 at 16:48
1  
Do the user names for the fields have to come from the stored procudure? This would be better off in the presentation to the user. –  JeffO Apr 23 '13 at 16:50
    
When requesting code please include the version of SQL being used –  Paul Jul 19 '13 at 9:30
add comment

migrated from programmers.stackexchange.com Apr 23 '13 at 16:57

This question came from our site for professional programmers interested in conceptual questions about software development.

3 Answers

You could try something like this.

declare @command varchar(8000)

set @command = 'select udf_0 as ' + (select udf_0
                                     from tbl_project_desc
                                     where project_name = @project) + 
               ' from table where column = value'

EXECUTE(@command)
share|improve this answer
    
This technically works, good suggestion, but the actual code is enormous and messy. Might end up being the answer if there are no more proposed solutions though. :\ –  Joshua Volearix Apr 26 '13 at 19:34
add comment

Please try the this code:
(it creates a stored procedure which will return what you need)

CREATE PROCEDURE [dbo].[GetProjectInfo]
    @Project varchar(100)
AS

DECLARE @command NVARCHAR(MAX)

SELECT @command = 'SELECT udf_0 AS ' + udf_0 + ',
    udf_1 AS '+ udf_1 + ',
    udf_2 AS '+ udf_2 + ',
    udf_3 AS '+ udf_3 + ',
    udf_4 AS '+ udf_4 + ',
    udf_5 AS '+ udf_5 + ',
    udf_6 AS '+ udf_6 + ',
    udf_7 AS '+ udf_7
FROM [TBL_PROJECT_DESC]
WHERE [PROJECT_NAME]=@Project

EXEC (@command)

GO

If that is not what you need please express your need.
If that is what you need please mark as answer :)

btw... Welcome to "Database Administrators" :)

share|improve this answer
add comment
SELECT [TABLE].[UDF_0] "Field Name 0",
       [TABLE].[UDF_1] "Field Name 1",
       [TABLE].[UDF_2] "Field Name 2",
       [TABLE].[UDF_3] "Field Name 3",
       [TABLE].[UDF_4] "Field Name 4",
       [TABLE].[UDF_5] "Field Name 5",
       [TABLE].[UDF_6] "Field Name 6",
       [TABLE].[UDF_7] "Field Name 7"
FROM [TABLE]

Works in Oracle...

Since you are building your command to Execute from C#. You could do a Select before the Execute of this command and then construct the SQL.

Pseudo Code:

fields = cmdWithFieldNames.Execute()
string sql = "SELECT [TABLE].[UDF_0] " + fields[0] + ",
           [TABLE].[UDF_1] " + fields[1] + ",
           [TABLE].[UDF_2] " + fields[2] + ",
           [TABLE].[UDF_3] " + fields[3] + ",
           [TABLE].[UDF_4] " + fields[4] + ",
           [TABLE].[UDF_5] " + fields[5] + ",
           [TABLE].[UDF_6] " + fields[6] + ",
           [TABLE].[UDF_7] " + fields[7] + "
    FROM [TABLE]"
results = cmd.Execute(sql)

There are easier ways of doing this at the presentation layer, however.

share|improve this answer
    
Right, that syntax works in mySQL as well, BUT it's not what I'm trying to achieve. The "Field Name i" changes based on every project, so it cannot be statically defined. Has to be pulled from a table to find the correct field name. –  Joshua Volearix Apr 25 '13 at 13:00
    
are you constructing the SQL in another language or does this have to be a pure sql solution? Like could you read your field definition table, then use the results of that to do the next query? –  Jeff Martin Apr 25 '13 at 21:47
    
The application interacts with c#, being called from an EXECUTE statement. I am much more familiar with C# than sql, so not entirely sure I follow your proposal... –  Joshua Volearix Apr 26 '13 at 19:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.