Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    16

    Unanswered: dynamic sql query

    I have hit another roadblock as I try to insert values into a table using dynamic sql. I am getting some values in a variable and I am trying to insert these variables into a a table using dynamic sql

    Here is a simple example of what I am doing

    create table tst(val varchar(60))
    declare @mysql varchar(60)
    select @mysql = 'its me'
    exec('insert into tst values('+@mysql+')')


    But it does not seem to work- I am not sure if this is even the right thing to do. Any ideas?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You need quotes around the string
    e.g.
    exec('insert into #tst values('''+@mysql+''')') -- quote within a quote
    OR
    exec('insert into #tst values("'+@mysql+'")') -- mixing double and single quotes
    OR
    select @mysql = '''its me''' -- quote within a quote
    exec('insert into #tst values('+@mysql+')')
    OR
    select @mysql = '"its me"' -- mixing double and single quotes
    exec('insert into #tst values('+@mysql+')')
    select @mysql = 'its me'

  3. #3
    Join Date
    Apr 2006
    Posts
    16

    update-temp table problem revisited

    Hi this solution works but here is an update to a problem that was just thrown up-

    I have a cursor within a cursor-say cursor2 within cursor1. The cursor2 is on a temporary table. I declare both these cursors initially. While declaring the cursor2 I also need to create the temporary table on which its declared.

    Then I open cursor1 in which I open cursor2 and loop through the records in cursor2 after which I need to drop the temp table and recreate it before cursor 2 opens again. However when I try to create the temp table again, the compilation errors out.

    Any suggestions as to how I can get around this problem?

  4. #4
    Join Date
    May 2006
    Location
    Garland, TX
    Posts
    2
    Do you really need to drop and recreate the table each time? Can you create the temp table outside your cursors, then just insert and delete all the records before and after you process cursor2?

    Seems like I ran into this a long time ago, and you can only create a temp table once inside a procedure or transaction.

  5. #5
    Join Date
    Apr 2006
    Posts
    16
    Yes I believe that should work. So thats why I am having troubles with the temp table in the stored proc. The problem is it does not throw an error of any sort and therefore kept me guessing...

    Thanks!

  6. #6
    Join Date
    Dec 2004
    Posts
    46
    try ur best not to use cursor, especially loop inside loops. If each loop takes say 100ms, which is typical. A 100 x 100 loop takes 1000 sec which is pretty long.

  7. #7
    Join Date
    Apr 2006
    Posts
    16
    But its unavoidable for me as I am trying to create a system which is completely generic and reads a table info to do everything.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •