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. :


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)


                           FROM [TBL_PROJECT_DESC]
                           WHERE [TBL_PROJECT_DESC].[PROJECT_NAME]=@Project),

Desired display would be :

Field Name 0 || Field Name 1 || etc...
  • 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

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'

| 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

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

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


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

EXEC (@command)


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" :)

| improve this answer | |
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"

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] + "
results = cmd.Execute(sql)

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

| 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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