Post reply

Dynamic SQL and referencing a temp table

  • February 13, 2006 at 8:00 am

    #111896

    Hi

    I want to check the values of my parameters in my Stored Procedure and write the SQL statement accordingly.

    I am writing Dynamic SQL and referencing a temporary Table in it, but I get an error that I must declare the @NewTable.

    I have following code:

    ---------------------------------------------

    CREATE PROCEDURE SearchAttributesTest

    --  search for these attributes

    @AttributeValueID1 int,

    @AttributeValueID2 int,

    @AttributeValueID3 int,

    @AttributeValueID5 int,

    @AttributeValueID6 int

    AS

    declare @RecordList table (RecordID int)

    DECLARE @NewTable TABLE (productid nvarchar(50), valueforType1 int, valueForType2 int, valueForType3 int,   valueForType5 int,  valueForType6 int )

    DECLARE @SQL NVarChar (4000),            

               @paramlist  nvarchar(4000)  

    Select @SQL = 'SELECT  ( p.ProductID), (p.ProductName)FROM  @XNewTable  nt inner join

     Viamed_Products p  on nt.productID = p.ProductID INNER  JOIN

                          Viamed_Products_Categories cat ON p.ProductID = cat.ProductID INNER JOIN  @XRecordList cr ON cat.CategoryID = cr.RecordID WHERE 1=1'

    if (@AttributeValueID1 < 0)

    Select @sql  = @sql + ' AND  nt.ValueForType1 =  @XAttributeValueID1'

    if @AttributeValueID2 <  0

    Select @sql  = @sql + ' AND  nt.ValueForType2 =  @XAttributeValueID2'

    -- DO this for 5 attributes   

    SELECT @paramlist = '                       

                            @XNewTable    int, 

                            @XRecordList    int, 

                            @XAttributeValueID1    int,                       

                            @XAttributeValueID2    int,                       

                            @XAttributeValueID3    int,                       

                            @XAttributeValueID5    int,                       

                            @XAttributeValueID6    int '

    --Return the result set

      EXEC sp_executesql @sql, @paramlist, @NewTable,  @RecordList @AttributeValueID1, @AttributeValueID2, @AttributeValueID3, @AttributeValueID5,

                          @AttributeValueID6

    END

    GO

    ------------------------------------

     

    However, I get an error that I must declare the @NewTable.

    I hope you can solve this problem

    Regards    

     

  • February 13, 2006 at 8:26 am

    #620459

    You probably have to define a temp table #NewTable instead a variable of table. The value (data) of the variable can not be passed between different scopes but a temp table can be.

  • February 13, 2006 at 9:03 am

    #620464

    Thanks for the reply. By using a temp table I have solved the problem.

    But whats the difference between Declare & Create :

    DECLARE @NewTable TABLE (...) AND

    create table #temp(...)

    'Create Table...' in Stored Procedure  doesn't create a physical table , does it???

  • February 14, 2006 at 5:33 am

    #620622

    DECLARE @New Table TABLE creates a variable of type table. All variables are created only in the current scope - that means, as soon as the procedure or batch is finished, they are cleared... and they can not be accessed from outside the scope. Variables are mostly created in memory (unless they are too big, I think). They are not part of a database, which can mean less locking and logging. Using table variables instead of temp tables can sometimes improve performance.

    Temporary tables (DECLARE #NewTable) are created in TEMPDB and they last as long as the connection lasts. They can be viewed or accessed only from within this connection, unles you declare them as global (DECLARE ##NewTable).

    CREATE TABLE in a stored procedure creates a physical table. If it is a # table, it only lasts as long as the connection, but it is created physically. If it is a normal table (like CREATE TABLE test (col1 int)), it is a permanent table that remains in the database.

  • February 14, 2006 at 7:15 am

    #620664

    Declare @table - procedure scope

    create table #table - connection scope

    create table sometable - multi connection scope

     

    exec('somestring') operates on different scope than the parent process

  • February 14, 2006 at 8:51 am

    #620721

    My dear friend ,

    if you want to create a temp table by using a variable name,

    then in each time, you should have to use a Dynamic Query..

    like

    set @STR='create table ' + @var_name + ' (

    col_1 varchar(10),

    col_2 varchar(20)

    )'

    exec (@str)

     

     

  • February 14, 2006 at 3:08 pm

    #620852

    If you create the table variable as described and then

    exec(@str)

    and then later

    exec(@str2)

    the table variable create in your first exec has already been destroyed, it's scope is only in the first exec statement. So you would have to create it and populate within that @STR.

  • February 14, 2006 at 11:40 pm

    #620897

    we have to excecute all at once.   so that all will be in one scope;

    declare @str1 varchar(100)

    declare @str2  varchar(100)

    set @str1 = 'declare @t table( t1 int) '

    set @str2 = 'insert into @t (t ) select tname from table name'

    set @str1 = @str1 +';' + @str2

    execute (@str1)

Viewing 8 posts - 1 through 7 (of 7 total)

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