How to create temp table from a dynamic query?

Question

I want to create dynamic query for paging [Main Motive], here is my code:

Alter proc proc_GetData
(
    @TableName varchar(500)='tblPropertyType',
    @PrimaryKey varchar(500)='Id',
    @Columns varchar(max)='PropertyType',
    @WhereCondition varchar(max)='',
    @RecsPerPage int =3,
    @Page int=1,
    @SortColumn varchar(500)='Id',
    @SortDirection Varchar(56)='asc'
)
as
DECLARE @SQL VARCHAR(MAX)
DECLARE @DSQL VARCHAR(MAX)
DECLARE @FirstRec int, @LastRec int
SET @FirstRec = (@Page - 1) * @RecsPerPage
SET @LastRec = (@Page * @RecsPerPage + 1)
select @FirstRec
select @LastRec
SET @SQL='
SELECT
ROW_NUMBER() OVER (ORDER BY ' + @SortColumn+ ' '+ @SortDirection +') RowNum,'+ @PrimaryKey +' , ' +@Columns + ' from ' +@TableName+ ' Where  1=1 '+
@WhereCondition

What I want to do is:

First: inert all the records in a temp table from the above query.
Second: SELECT * FROM @TEMPResult WHERE RowNum > @FirstRec AND RowNum < @LastRec.

Please help me

score -6 · Accepted Answer · edited Oct 03 '14 at 19:00

-6

SET @SQL='SELECT ROW_NUMBER() OVER 
   (ORDER BY ' + @SortColumn+ ' ' + @SortDirection + ') RowNum, ' + @PrimaryKey + ', ' + @Columns + ' 
   INTO tempTableName
   FROM ' + @TableName + ' Where  1 = 1 ' + @WhereCondition

edited Oct 03 '14 at 19:00

C-Pound Guru

15,967
6
46
67

answered Mar 25 '13 at 11:48

Jimbo

2,529
19
22

4

Do you realize that `INTO tempTableName` isn't a temp table? – Taryn Oct 03 '14 at 19:18
@bluefeet but it's *named* temp which is what matters, right? that way nobody else can use it. /sarcasm – swasheck Oct 03 '14 at 19:20
if he would rather not drop the temporary table after use he can prepend it with # - but downgrading the answer for nit picking is a little disappointing... – Jimbo Oct 03 '14 at 20:51
4

Downvoting is for the benefit of the wider audience. This query cannot be run by multiple users simultaneously. Using an actual temporary table (with a name prefixed with a `#`) would fix that issue but then you would be unable to access that table outside the dynamic query's scope. – Andriy M Oct 04 '14 at 09:17
2

How is it nit-picking? The question clearly asks for a temporary table. Your answer should either create one or explain why this isn't needed or isn't possible or isn't advised (whatever your advice is.) An answer with just code is bareful helping. – ypercubeᵀᴹ Oct 04 '14 at 12:20
you define a temp table as a table prefixed by a # I define a temp table as any table that is in use temporarily, included tables prefixed by # – Jimbo Oct 04 '14 at 15:50

How to create temp table from a dynamic query?

1 Answers1

Linked