store result of query into a variable

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • KritiGuleria
    New Member
    • Sep 2007
    • 13

    #1

    store result of query into a variable

    i need to put the result of quey @st into a variable. could anyone please tell me how to do that?


    select @sys= + columnName+', ' from tab
    set @sys=left(@sys, len(@sys)-1)
    select @sys

    DECLARE cursor_insert CURSOR FOR
    SELECT insequenceid FROM temp_ac
    OPEN cursor_insert
    FETCH NEXT FROM cursor_Insert into @inseq

    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @st= ('select ' + @sys + ' from temp_ac ')
    execute (@st)
    FETCH NEXT FROM cursor_Insert into @inseq
    END
    CLOSE cursor_insert
    DEALLOCATE cursor_insert

    i want something so that @val= execute(@st)??? ??
  • davef
    New Member
    • Sep 2007
    • 98

    #2
    Somethig like this will do:

    declare @sql varchar(100),
    @szDesc varchar(50)

    --Create a test table and dump some data into it
    create table Account (
    nID int NOT NULL PRIMARY KEY,
    szDesc varchar(50)
    )
    insert into Account
    select 1, 'Primary'
    insert into Account
    select 2, 'Secondary'
    insert into Account
    select 3, 'Offshore'

    --Declare a table variable corresponding in structure to Account table
    declare @temp table (
    nID int NOT NULL PRIMARY KEY,
    szDesc varchar(50)
    )
    set @sql='select * from Account'
    insert into @temp
    exec (@sql)

    --Store result of dynamic SQL query in a variable
    select @szDesc=szDesc from @temp where szDesc like 'P%'
    print @szDesc

    --Drop the test table
    drop table Account

    Comment

    • KritiGuleria
      New Member
      • Sep 2007
      • 13

      #3
      this is not working.
      i'm getting the error
      "EXECUTE cannot be used as a source when inserting into a table variable"

      moreover i cannot use temporary table as i dont kno the exact number of columns that are to be fetched as those columns will be selected on runtime only.

      Comment

      • azimmer
        Recognized Expert New Member
        • Jul 2007
        • 200

        #4
        Originally posted by KritiGuleria
        i need to put the result of quey @st into a variable. could anyone please tell me how to do that?


        select @sys= + columnName+', ' from tab
        set @sys=left(@sys, len(@sys)-1)
        select @sys

        DECLARE cursor_insert CURSOR FOR
        SELECT insequenceid FROM temp_ac
        OPEN cursor_insert
        FETCH NEXT FROM cursor_Insert into @inseq

        WHILE @@FETCH_STATUS = 0
        BEGIN
        set @st= ('select ' + @sys + ' from temp_ac ')
        execute (@st)
        FETCH NEXT FROM cursor_Insert into @inseq
        END
        CLOSE cursor_insert
        DEALLOCATE cursor_insert

        i want something so that @val= execute(@st)??? ??
        With EXEC you open a new environment: that is you cannot directly move data in and out of the EXEC scope. If you can process @val within the EXEC scope (that is: it is not utterly complex or a return value) I suggest you define and process it within @st like this:
        change this:
        set @st= ('select ' + @sys + ' from temp_ac ')
        to this:
        set @st= ('select @val=' + @sys + ' from temp_ac ')
        (append to your code):
        Code:
        set @st='declare @val varchar(255); '+@st
        set @st=@st+'; print @val;'    ' or whatever your processing is
        now EXEC(@st)

        There can be tricky workarounds to this but if it works look no further.

        Comment

        Working...