Dynamic SQL and referencing a temp table

[johnsmith180]

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

 

[SQLDenis]

AFAIK all the parameters used with sp_executesql have to be ntext or be able to convert to ntext and a table variable won't work in this case

use a #temp temp table and use dynamic SQL instead of sp_executesql as a workaround

small example

create table #temp(id int)
insert into #temp
select 1 union select 2

declare @SQL varchar(500), @tablename varchar(50)
select @tablename ='#temp'
select @SQL ='select * from ' + @tablename
print @SQl -- for testing
exec (@SQL)

more about dynamic SQL here (

http://www.sommarskog.se/dynamic_sql.html)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[johnsmith180]

Thanks for the help. 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 SP doesn't create a physical table , does it???

 

[SQLDenis]

@NewTable is a table variable and you create it with declare
#temp is a temp table created in the temp database and will be dropped after the code has finished

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/