Post reply

Dynamic SQL - Assign Variable Value

  • August 30, 2007 at 10:33 am

    #120194

    SQL 2000

    I need to assign a value to a variable using Dynamic SQL.  The value is dependant upon a dynamic tablename, which is why I need dynamic SQL. 

    For instance,

    @strValue = EXEC('SELECT FieldValue FROM ##tmpTable' + @counter)

    What is the syntax to do this?

    Thank yoU!!

  • August 30, 2007 at 10:51 am

    #730511

    You need to use sp_executesql instead of exec.

    set @sqlstring = 'select @recIDmin = min(record_id) from ' + @TblName

    --get min value of record_id in a tbl into var @recIDmin

    exec sp_executesql @sqlstring, N'@recIDmin int OUTPUT',@recIDmin OUTPUT

     

    Check out this previous thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=202194

     

  • August 30, 2007 at 11:14 am

    #730516

    Thank you very much!  I now have this working and no longer pulling hair out.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply