Dynamic SQL and a temporary table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL and a temporary table

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-10 : 11:35:06

Hi
Today, I have get alot of help from this forum.

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 SearchAttributes

@AttributeValueID1 int,
@AttributeValueID2 int,

AS

declare @RecordList table (RecordID int)

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

-- holds sql statement

Declare @Values Varchar(500)
Select @Values = ' '
if (@AttributeValueID1 < 0)
Begin
Select @Values = @Values + 'WHERE nt.ValueForType1 =' + @AttributeValueID1
END

if @AttributeValueID2 < 0
Begin
Select @Values = @Values + 'AND nt.ValueForType2 =' + @AttributeValueID2
END

DECLARE @SQL VarChar (1000)

Select @SQL = 'SELECT ( p.ProductID) FROM' + @NewTable + ' nt inner join
Products p on nt.productID = p.ProductID INNER JOIN' +
@RecordList + ' cr ON p.CategoryID = cr.RecordID' + @Values +' ORDER BY p.ProductID'
..........
Exec (@SQL)

Some how it does not complaint about @RecordList, but only @NewTable
Regards

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-10 : 11:43:13

neither of your table variables are "in scope" of the dynamic statement. The @NewTable is just the 1st error that occured so that is the one reported in the error message.

Be One with the Optimizer
TG

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-10 : 11:53:50

So what can I do to run this sort of Query. Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-10 : 14:17:31

I don't see the need for a dynamic statement. Why can't you just do a non-dynamic parameterized query?

Be One with the Optimizer
TG

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-13 : 04:43:43

The reason why I am trying to use dynamic statement is because I want to run different sql statements.
For instance, if @AttributeValueID1=0, then I want to run:

Select * from Products where AttributeValueID2=@AttributeValueID2

If @AttributeValueID1 & @AttributeValueID2 are NOT 0 , then I want to run:

Select * from Products where AttributeValueID1=@AttributeValueID1 AND AttributeValueID2=@AttributeValueID2

The reason why I am trying to eliminate "0" from my sql statement is because I dont have "0" within my data, so if I run:
Select * from Products where AttributeValueID1=0

This will not return any result.

Kind regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 04:52:42

The simple way is


If  @AttributeValueID1=0
	Select * from Products where AttributeValueID2=@AttributeValueID2
else If  @AttributeValueID1 <>0 and  @AttributeValueID2 <> 0 
	Select * from Products where AttributeValueID1=@AttributeValueID1 AND AttributeValueID2=@AttributeValueID2

Madhivanan

Failing to plan is Planning to fail

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-13 : 05:36:11

Yes, madhivanan, Your solution would work. However, in future, if I add more "AttributeValueIDs" than this solution would become too difficult to main. Just imagine the following:

@AttributeValueID1 = 0
@AttributeValueID2 = 1
@AttributeValueID3 = 1
@AttributeValueID4 = 0
@AttributeValueID5 = 1

Writing If...Else statements will become too cumbersom
Thanks

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-14 : 05:40:42

For the interest of others who may have the same problem, the solution is to use a temp table.

Use:
create table #RecordList (RecordID int)

instead of:
declare @RecordList table (RecordID int)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources