How to read from a table that is ''passed in'' as a parameter

  • I have a sproc, similar to that listed below, that has a input parameter of a table name. I want to do a SQL on this table and saved the output to a #Temp table. However, the #Temp table disappears after the "exec".

    I don't want to create a ##Temp table becuase of potential conflict if the sproc is run by 2 connections at the same time. I prefer not to create the #Temp table and "insert" into it. What is the simplest way to create this #Temp table?

    create procedure TestInputTable (@InputTable varchar(100))

    as begin

    exec ('select * into #TempTable from ' + @InputTable)

    -- the follwoing 2 lines fail because #TempTable not exists

    select * from #TempTable where Id < 100
    select * from #TempTable where Id >= 1000

    end

  • How about putting the whole statement in one string and then executing that?  Something like this:

    create procedure TestInputTable (@InputTable varchar(100))

    as begin

     declare @strSQL varchar(8000)

     set @strSQL= 'select * into #TempTable from ' + @InputTable

     set @strSQL= @strSQL+ ' select * from #TempTable where Id < 100 select * from #TempTable where Id >= 1000'

     exec (@strSQL)

    end

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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