Dynamic SQL and referencing a temp table

Question

Dynamic SQL and referencing a temp table

hasan-279291

Old Hand

Points: 304
More actions
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

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

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

DHuang Valued Member Points: 51 More actions · Answer 1

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.

hasan-279291 Old Hand Points: 304 More actions · Answer 2

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???

Vladan SSC-Insane Points: 22072 More actions · Answer 3

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.

GeBlack Ten Centuries Points: 1363 More actions · Answer 4

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

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 5

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)

GeBlack Ten Centuries Points: 1363 More actions · Answer 6

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.

Vee Hall of Fame Points: 3028 More actions · Answer 7

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)