Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Unanswered: Using a pointer to a local varaiable in Dynamic SQL

    I am having trouble with dynamic SQL.
    Early in my code I assign data to a bunch of local variables.
    I want to access these later in my code and use the data values.
    The code example below shows a simplified example to explain what I am trying to do.

    -- ----------------------------------------------
    -- Declare and set the data into a local variable
    -- ----------------------------------------------
    DECLARE @SD1 real
    SET @SD1 = 1.1

    -- ----------------------------------------------------------
    -- Declare and set a variable to point to data local variable
    -- ----------------------------------------------------------
    DECLARE @SDName varchar
    SET @SDName = '@SD1'

    -- ---------------------------------------
    -- Declare and set the dynamic SQL command
    -- ----------------------------------------
    DECLARE @SQLCmd varchar
    SET @SQLCmd = 'SELECT MyNumber = ' + @SDName

    By running this code the @SQLCmd contains the following ...
    SELECT MyNumber = @SD1

    BUT what I REALLY want is for @SQLCmd to contain this ...
    SELECT MyNumber = 1.1

    How can I accomplish this?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK

    FIRST

    "Do you know how to handle that weapon soldier?"

    You need to understand that dynamic (read bad for most applications) is executed in it's own independent thread.

    When You declare a variable (and they're all local) they exist in the context of the initiated script/sproc

    When you execute dynamic sql from that script, the two can't see each other (without some hacks)

    So, take a step back

    Tell us what data you have

    SELECT '1','1','3' UNION ALL
    SELECT '1','1','3' UNION ALL
    SELECT '1','1','3' UNION ALL
    SELECT '1','1','3'

    What you need done to it

    And what it should look like in the end

    A beautiful rainbow

    Probably get you a solution in minutes
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    this part was amusing though

    DECLARE @SDName varchar
    SET @SDName = '@SD1'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Tags for this Thread

Posting Permissions

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