none
how can i loop in sql query

    Question

  • hi , 

    i want to loop a value from satement below 

    select ch from chp  

    i want to loop till ch has a value in T-SQL and inside the loop 

    i will use the value for the satement below 

    UPDATE MSF
    SET MC = 

    CASE  WHEN [1] = 'PC' OR [1] = 'PA' THEN 1  Else 0 END+
    CASE  WHEN [2] = 'PC' OR [2] = 'PA'  THEN 1  Else 0 END

    from [ch_p]

    WHERE
         [ch_p].SID = [Final].SID

    Tuesday, June 16, 2009 10:31 AM

Answers

  • Devpan,

    You can't use static SQL to select from a variable table or column name.  To do this, you need to store the text that you want to execute as a string, then dynamically execute it, like this:

    SET @sql = 'UPDATE msf SET mc = CASE ......   '
    SET @sql = @sql + ' FROM [' + @ch + '] WHERE ......'

    EXEC(@sql)

    Michael Asher
    Tuesday, June 16, 2009 11:19 AM

All replies

  • From what I can see you don't need a loop.

    if you do then the dreaded cursor will do on the SELECT ch from chp

    however why dont you try


    UPDATE MSF
    SET MC = 

    CASE  WHEN [1] = 'PC' OR [1] = 'PA' THEN 1  Else 0 END+
    CASE  WHEN [2] = 'PC' OR [2] = 'PA'  THEN 1  Else 0 END

    from [ch_p]

    WHERE
         [ch_p].SID = [Final].SID and ( "till ch has a value" expressed a as a true false)
    Tuesday, June 16, 2009 10:40 AM
  • i tried with store procedure as the case are almost 560 since the columns are that much , its working for single ch , but i wan to run loop so that every time i should not change the value of from [ch_p] where [ch_p] , both places it should take dynamically.

    kindly suggest.
    Tuesday, June 16, 2009 11:05 AM
  • i have also tried looping with cursor

    DECLARE @ch varchar(max)


    DECLARE @chvalue CURSOR

    SET @chvalue = CURSOR FOR
    SELECT ch FROM chtab

    OPEN @chvalue

    FETCH NEXT
    FROM @chvalue INTO @ch

    WHILE @@FETCH_STATUS = 0

    BEGIN

    UPDATE MSF
    SET MC = 

    CASE  WHEN [1] = 'PC' OR [1] = 'PA' THEN 1  Else 0 END+
    CASE  WHEN [2] = 'PC' OR [2] = 'PA'  THEN 1  Else 0 END

    from '[' + @ch + '_p]'

    WHERE
         '[' + @ch_p + ']'.SID = [Final].SID


    FETCH NEXT
    FROM @chvalue INTO @ch

    END

    i am getting error with incorrect syntax
    Tuesday, June 16, 2009 11:12 AM
  • Devpan,

    You can't use static SQL to select from a variable table or column name.  To do this, you need to store the text that you want to execute as a string, then dynamically execute it, like this:

    SET @sql = 'UPDATE msf SET mc = CASE ......   '
    SET @sql = @sql + ' FROM [' + @ch + '] WHERE ......'

    EXEC(@sql)

    Michael Asher
    Tuesday, June 16, 2009 11:19 AM
  • Tuesday, June 16, 2009 11:24 AM
  • Devplan,

    TSQL is TSQL, whether executed statically or dynamically.  And in neither case will it allow you to exceed the row size limit imposed by SQL Server.

    The only way around that is to narrow the row; I suggested a few of the ways you can do that in your other thread.

    Michael Asher
    Tuesday, June 16, 2009 11:26 AM
  • can you give some samples of your input table(S) and what you want as a output.

    Tuesday, June 16, 2009 11:34 AM
  • Hi devpan,

    code segment below should get you out of the syntax issue. i cant figure out what [Final] is. will leave that one with you to sort it out.

    declare @ch varchar(1000)
    declare @chvalue cursor
    set @chvalue = cursor for select ch from chtab
    open @chvalue
    fetch next
    from @chvalue into @ch
    while @@FETCH_STATUS = 0
    begin
    exec ('UPDATE MSF
    SET MC =
    CASE  WHEN [1] = ''PC'' OR [1] = ''PA'' THEN 1  Else 0 END+
    CASE  WHEN [2] = ''PC'' OR [2] = ''PA''  THEN 1  Else 0 END
    from [' + @ch + '_p]
    WHERE
         [' + @ch + '_p].SID = [Final].SID')
    FETCH NEXT
    FROM @chvalue INTO @ch
    END

    HTH,
    Huiyong

    Tuesday, June 16, 2009 2:38 PM