table variable and dynamic sql !!

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

Transact-SQL (2000)

table variable and dynamic sql !!

Author

Topic

m.abdullah
Starting Member

7 Posts

Posted - 2008-03-16 : 04:27:46

i have a stored procedure that making paging and i need it to make sorting the

CREATE PROCEDURE [dbo].[Category_pager]
(
   @startRowIndex      int,
   @maximumRows      int,
   @sortedBy varchar(50),
   @TotalRecords   INT out	
)
AS


--Create a table variable
DECLARE @TempItems TABLE
(
   IndexID int IDENTITY,
   catID int
   PRIMARY KEY (IndexID)
)

 INSERT INTO @TempItems (catID) 
 		     SELECT [Category].id  FROM [Category] 

select @TotalRecords= @@ROWCOUNT

---Now, return the set of paged records
SELECT c.id, c.categoryName
FROM @TempItems t
   INNER JOIN Category c ON
     c.id = t.catID

WHERE IndexID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
GO

i want to add the @sortedBy to the select statement in the red paragraph
so i did that

Declare @sql nvarchar(4000)
set @sql =' INSERT INTO @TempItems (catID)  SELECT [Category].id  FROM [Category]  ORDER BY  '+@sortedBy

it gives me error
i think that error because i can't use table variable in exec insert
any help please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-16 : 04:30:59

use temp table

KH
[spoiler]Time is always against us[/spoiler]

m.abdullah
Starting Member

7 Posts

Posted - 2008-03-16 : 04:55:02

temp table work fine and iam really using it but to improve performance i want to use table variable
is it possible to use it with dynamic sql

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-16 : 07:31:08

table variable only exists within the scope, in your case, it is not.

How much data do you have in the table variable ? I don't think the performance different between temp table and table variable is so great that you really have to resort to table variable.

KH
[spoiler]Time is always against us[/spoiler]

m.abdullah
Starting Member

7 Posts

Posted - 2008-03-16 : 09:07:55

ok i think i will stick to temp table ;)
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources