Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    8

    Unanswered: store results of dynamic sql to temp table

    Hi,

    I am using Sybase ASE15.x version. In my stored procedure I have basically 8 sql strings stored in different varchar variables. I have to perform a UNION of all the 8 queries. If I put up all these 8 queries joined by UNION in a single varchar variable and then try to execute the string I am facing problem since the sql query is longer than the max number of chars allowed in a varchar.

    Can we split up the finalquery into parts and store the results of the parts in temporary tables and then perform a UNION on these temporary tables? If I do this i get an error.

    What i do is something like this.

    declare @sql1 varchar(8000),
    @sql2 varchar(8000),
    @sql3 varchar(8000),
    @sql4 varchar(8000),
    @sql5 varchar(8000),
    @sql6 varchar(8000),
    @sql7 varchar(8000),
    @sql8 varchar(8000)
    @finalQuery varchar(16384)

    select @sql1 = 'SELECT ...'
    select @sql2 = 'SELECT ...'
    select @sql3 = 'SELECT ...'
    select @sql4 = 'SELECT ...'
    select @sql5 = 'SELECT ...'
    select @sql6 = 'SELECT ...'
    select @sql7 = 'SELECT ...'
    select @sql8 = 'SELECT ...'

    select @finalQuery = @sql1+' UNION '+@sql2
    create table #t1 (id INT, filter varchar(225))
    insert into #t1 exec(@finalQuery)

    select @finalQuery = @sql3+' UNION '+@sql4
    create table #t2 (id INT, filter varchar(225))
    insert into #t2 exec(@finalQuery)
    ...

    SELECT * from #t1
    UNION
    SELECT * from #t2


    This shows an error incorrect syntax near exec. Where am i going wrong? Is this supported in ASE? How do i get the results of dynamic sql into temp tables for further processing?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Try
    exec ('insert into #t1 '+@finalQuery)

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
  •