Select statement with Field Names listed in another table

Posted on 2006-05-31
Last Modified: 2007-12-19
I have a table that lists a bunch of field names from multiple tables.  I have the names setup with the table.fieldname so that I can draw information Example:

I also have the from statement built that has all the joins necessary to grab the data from these field names.

This sql statement/stored procedure will also have where and order by statements that will need to be appended to the end of the procedure.

I need to know the best way to do this.

An good and clear example would be great and would be worth 500 pts. to me

Question by:Sheritlw
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    declare @fields table (FiledName varchar(255))

    declare @op nvarchar(1000)
    SELECT @op = COALESCE(LTRIM(@Op)+',','')+FiledName
    FROM @fields

    EXEC('SELECT '+@op+ ' FROM sysobjects ')
    LVL 11

    Accepted Solution

    Just to give an idea may have to do this way....(I've not put the syntax)

    Declare @SQL varchar(8000)

    select @sql = 'SELECT '

    declare cursor fieldcol from fieldtable

    --for each row in cursor loop through and add it to the select
      select @sql = @sql + @fieldcol + ','
    --until the end of loop

    select @sql = @sql + @FromClause

    select @sql = @sql + @whereclause

    Select @sql = @sql + @orderbyclause


    LVL 50

    Expert Comment

    why do you want to use dymanic sql to query your database...?

    Static SQL is usually best from a performance and security viewpoint..!

    or are you just trying to generate your Stored Procedures?


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    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.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now