SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Dynamic SQL Help

    I've got a table with a bunch of columns named "Week1", "Week2", "Week3", "Week4", etc. After determining the week number, I need to update a specific column in the table.
    Code:
    -- @Week is varchar with a single number in it: 1, 2, 3, etc.
    -- @EntryID is input parameter
    
    -- append "Week" to @Week
    SET @Week = 'Week' + @Week
    
    -- update pick
    SET @sql = 'UPDATE picks SET ' + @Week + ' = ' + @UserPick + ' WHERE EntryID = ' + @EntryID
    
    EXEC @sql
    This is not working, though. I get this error message:
    Code:
    Syntax error converting the varchar value 'UPDATE picks SET Week1 = ' to a column of data type int.
    The "Week1", "Week2", etc. columns are of the data type integer. I'm running SQL Server 2000.

    Can someone help me out with this?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if it's a stored proc, just use IF logic

    pseudocode:
    Code:
    if @week = 1
      update picks set week1 = @userpick
    elseif @week = 2
      update picks set week2 = @userpick
    et cetera
    these go up to 17, right?

    i've got a similar database, and i normalized it so that each week is a separate row

    sorry for the pseudocode, i don't write stored procs (never needed to; always do it in ordinary sql)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm guessing that the @UserPick variable is not a string type.

    You'll just need to convert the the @UserPick variable to a string type so you can append it to the @sql string variable.

    You my also need to do this with the @EntryID variable if that's not a string type either.

    Code:
    ...
    -- update pick
    SET @sql = 'UPDATE picks SET ' + @Week + ' = ' + CONVERT(VARCHAR(10), @UserPick) + ' WHERE EntryID = ' + @EntryID
    ...
    Last edited by shane; Jul 26, 2002 at 13:38.

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    yeah, i got it

    I did it up like so:
    Code:
    SET @sql = 'UPDATE picks SET ' + @Week + ' = ' + cast(@UserPick as varchar(10)) + ' WHERE picks.EntryID = ' + cast(@EntryID as varchar(10))


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
  •