Professional Web Applications Themes

Dynamic column names - Microsoft SQL / MS SQL Server

I am using 7.0 and trying to generate a sql statement that allows me to a select a column that is defined as a variable. Basically something like in a stored procedure...... Declare column varchar (30) Declare value varcar (30) set column = 'fname' select value = column from namelist where lname = 'williams' What I get back is that value 'fname' not the first name I was searching for. If I try to set up the string and to an exec() the statement runs, but I cannot store result, I receive an error that value was not declared. How ...

  1. #1

    Default Dynamic column names

    I am using 7.0 and trying to generate a sql statement that
    allows me to a select a column that is defined as a
    variable.

    Basically something like in a stored procedure......
    Declare column varchar (30)
    Declare value varcar (30)

    set column = 'fname'

    select value = column from namelist where lname
    = 'williams'

    What I get back is that value 'fname' not the first name I
    was searching for. If I try to set up the string and to an
    exec() the statement runs, but I cannot store result, I
    receive an error that value was not declared.

    How do I dynamically change column names in a select
    statement.

    Thanks in advance for any help.
    John Williams
    John Guest

  2. #2

    Default Re: Dynamic column names

    use sp_executesql
    Try something like this:

    Declare DynamicSql nvarchar(1024), TotalRecords int ,TableName
    varchar(256)
    select tablename='customers'
    select DynamicSql = 'Select TotalRecords = COUNT(*) FROM '+ TableName
    exec sp_executesql dynamicsql, N'TotalRecords int output', TotalRecords
    output
    print totalrecords

    --
    -Vishal

    "John Williams" <com> wrote in message
    news:0c7f01c35109$a31f5140$gbl... 


    Vishal Guest

  3. #3

    Default Re: Dynamic column names

    Read this article:
    http://www.algonet.se/~sommar/dynamic_sql.html

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "John Williams" <com> wrote in message
    news:0c7f01c35109$a31f5140$gbl... 


    Jacco Guest

  4. #4

    Default But... Re: Dynamic column names

    Vishal,
    Thanks for the feedback, I have had success in
    substituting table names, but what I haven't been able to
    do is column names. When I use the sp_executesql
    I tried
    sp_executesql
    N'select value = fname from namelist where
    lname='williams',
    N'fname varchar(30),
    N'value varchar(30),
    N'fname = 'fname'

    I get the value must be declared.
     
    int ,TableName 
    FROM '+ TableName 
    output', TotalRecords [/ref]
    that [/ref]
    name I [/ref]
    an 
    >
    >
    >.
    >[/ref]
    John Guest

  5. #5

    Default Re: Dynamic column names


    Take a look at this: http://www.algonet.se/~sommar/dynamic_sql.html


    --
    --

    Un saludo

    --
    --
    ----------------------------------------------
    "Sólo sé que no sé nada. " (Sócrates)

    Por favor, responder únicamente al foro

    (Guía de netiquette del foro)
    http://www.helpdna.net/bosqlfaq00.htm
    http://perso.wanadoo.es/rubenvigon/foro

    (FAQ's de SQL Server)
    http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp
    http://www.helpdna.net/bosqlfaq.htm

    "John Williams" <com> escribió en el mensaje
    news:0c7f01c35109$a31f5140$gbl... 


    Carlos Guest

Similar Threads

  1. Getting column data without column names
    By Jared@Itron in forum Coldfusion Database Access
    Replies: 4
    Last Post: January 18th, 09:26 PM
  2. counting column and getting column names
    By dduck1934 in forum Coldfusion Database Access
    Replies: 0
    Last Post: May 24th, 02:05 PM
  3. Move bound column to right of dynamic column in datagrid?
    By John E. in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: March 28th, 01:29 PM
  4. How to get column names using SQL?
    By Gert van der Kooij in forum IBM DB2
    Replies: 7
    Last Post: July 11th, 04:36 PM
  5. No column names
    By Vesna Martinovic in forum IBM DB2
    Replies: 0
    Last Post: July 3rd, 07:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139