No Udation when using a variable

• SQLRO

  SSCrazy

  Points: 2923

  More actions

  January 29, 2006 at 11:09 pm

  #89255

  Hi,

    I am using a stored procedure to update the column name dynamically.

  Name of the column is actually a value in another view. The query is

   

  Declare @varName varchar(255)

  DECLARE MIS_Cursor_UPD CURSOR

  For

  Select Distinct <Col_name> from tableName

  Open MIS_Cursor_UPD

  Fetch Next from MIS_Cursor_UPD INTO  @varName

  WHILE @@Fetch_STatus =0

  BEGIN

  Execute('

  update

  ##<Tablename>

  set [' +  @varName + '] = v.records

  from

  ##<Tablename> t,

  <Viewname> v

  where

   ltrim(rtrim(v.<ColumnName&gt) = ltrim(rtrim(' +  @varName + '))

  It shows 0 records updated for each updation,.But I do the same thing in a query by replacing the variable name with it's value then it is updating the value.

  Thanks in Advance

  Rohit

   

   

   

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  January 30, 2006 at 11:26 am

  #617787

  Rohit - couple of things...

  1) Your variable is obviously not storing anything - a quick PRINT @varName should confirm this for you.

  2) Why're you using "distinct" to select column names from a table...besides you should be querying the system views for this kind of info...this query will give you all the columns you have in table xyz...

  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tblXYZ'
  

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• SQLRO

  SSCrazy

  Points: 2923

  More actions

  January 30, 2006 at 11:34 pm

  #617879

  Thanks for your reply.It was all about concatenating the variable in Execute procedure of SQL server. After I got the concatenation right now it is working fine. Another thing I did is removed distinct keywork to make the cursor updatable one.

   

  Thank You.

  Rohit