Do you know how to use SQL to make an 'indirection

• AlenaB

  SSC Veteran

  Points: 264

  More actions

  November 20, 2001 at 7:49 am

  #78356

  Do you know how to use SQL to make an 'indirection'. You remember in the C language, how you can treat the contents of a variable as a pointer to another variable location, or in other 4th generation languages (& some OS stuff), you can use a 'handle' to hold an indirect reference to another variable...

  I want to do something similar in SQL. I want a column in a table to hold a string that is a column name. I want to create an SQL statement that will use the value of that column name and perform a comparison.

  For example, there it a table:

  Table: T_Compare_Columns

  ( col_name char(50) )

  Another table:

  Table: T_Datastuff

  ( name_ch char(20),

  county_ch char(20),

  salary_dc decimal ( 12,2) )

  Insert a line into T_Compare_columns:

  Insert into T_Compare_columns

  values ( 'county_cd' )

  Then use that to perform an 'indirect reference' comparison. This statement is pure fiction. I know it will not work in this form:

  select *

  from T_Datastuff

  where T_Compare_column.col_name = T_datastuff.COLUMN_NAME

  and t_datastuff.COLUMN_NAME = 'RICHLAND'

  Alena B

  
  Alena B

• nigelrivett

  SSCertifiable

  Points: 5472

  More actions

  November 20, 2001 at 3:46 pm

  #420251

  I think the only way to do this is via dynamic sql

  
  Cursors never.
  DTS - only when needed and never to control.
  

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738112

  More actions

  November 20, 2001 at 7:21 pm

  #420258

  Yes, you can only do this with dynamic sql.

  Steve Jones

  steve@dkranch.net

• rb

  SSC Enthusiast

  Points: 179

  More actions

  November 22, 2001 at 3:13 am

  #420330

  What they mean by Dynamic SQL is that ,

  Decide what Ideally your SQL should look like.

  I think there is still some haziness over there.

  Then decide what is dymanic in that SQL and

  what can be passed as parameters to a stored procedure

  Thirdly set a varchar variable to that ideal SQL u want.

  Then code the stored procedure to use sp_execute sql to fire the query.

  Regards,

  RB