Trying to use variable to get column data.

• Karen Rice

  SSC Enthusiast

  Points: 152

  More actions

  February 19, 2004 at 10:25 am

  #84577

  Hi,

  I'm not a SQL pro at all and I was trying to generate one SQL statement that reads one table to get the table name and then updates that table with information.

  TableList (table) contains the following columns:  TableId - integer, TableName - nvarchar(50), NextId - integer

  I want to read through all the tables in TableList, and return the Max value of one of the columns in that table.  I then want to update that Max value back to the TableList.  An additional problem is that the column name I want to get the Max of will be genned on the fly (as the column name does not exist in TableList and is dependant on the TableName).  I will be appending some characters with the tablename and getting the Max of that column.  Am I making any sense at all?

  Any help would be much appreciated.  Thanks.

  Karen

   

• fhanlon

  SSChampion

  Points: 13131

  More actions

  February 19, 2004 at 11:39 am

  #494759

  How about something like:

  DECLARE @sql nvarchar(2000)

  DECLARE @out integer

  --loop through all tables in tablelist

  DECLARE @objName VARCHAR(50)

  SET @objName = ''

  WHILE @objName IS NOT NULL

   BEGIN

    SELECT @objName = MIN( TableName )

    FROM TableList

    WHERE TableName > @objName

    IF @objName IS NOT NULL

     BEGIN

      -- Insert code to do stuff here.

      SET @sql = 'SELECT @out = MAX(' + @objName + '123) FROM ' + @objName

      SELECT  @sql

      EXEC sp_executesql @sql,,N'@out Int OUTPUT',@out OUTPUT

     END

   END

   

  You will have to correct the column name as I just appended 123 to the table name to create a column  name, but this gives you an idea.  The variable @out is available after the sP_executesql statement and you can then write an INSERT statement to insert this value in the TableList table using the @objectName as a key.  Hope this gives you a start.

  Francis