CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2012
SH.Developer
SH.Developer

Reputation: 187

error in sql query result

I have this query

DECLARE @Base nvarchar(200) 

SET @Base = 'WITH Base AS (SELECT Id, ROW_NUMBER() OVER (ORDER BY Id DESC) RN FROM'
                        + Quotename(@SampleWorkTbl) + ')
                        SELECT * INTO ##temp FROM Base'

EXEC (@Base)

SELECT * FROM   ##temp

declare @command nvarchar(max)

Set @command='SELECT TOP 15 [Name],[ImageAddress],(SELECT TOP 1 COUNT(Id) FROM' + QUOTENAME(@SampleWorkTbl) + ') as AllSampleCount FROM ' + QUOTENAME(@SampleWorkTbl) + 
            ' WHERE [Id] IN (SELECT TOP 15 Id From ##temp WHERE RN > ((@Count-1)*15) ORDER BY Id DESC) ORDER BY Id DESC'

exec (@command)

drop table ##temp

I want get [Name],[ImageAddress] and count of Id from any table name that pass to the procedure

but instead of get name , image address and Id I get this data

Id  RN
10   1
9    2
8    3
7    4
6    5
5    6
4    7
3    8
2    9
1    10

and when try again i get this error 

There is already an object named '##temp' in the database.

@SampleWorkTbl is a name of any table that pass to the this query

how i can fix this problem ?

thank you for you help

Upvotes: 0

Views: 61

Answers (2)

Dgan
Dgan

Reputation: 10285

try this:

For

There is already an object named '##temp' in the database.

Before Creating New Temp Table You need to Use This:

if  (object_ID('tempdb..##temp')) is not NULL
DROP TABLE ##temp

You need to add [Name],[ImageAddress],ID these columns to get desire output

 SET @Base = 'WITH Base AS (SELECT [Name],[ImageAddress],ID,COUNT(ID) CntID, ROW_NUMBER() OVER (ORDER BY Id DESC) RN FROM'
                        + Quotename(@SampleWorkTbl) + ' GROUP BY [Name],[ImageAddress],ID)
                        SELECT * INTO ##temp FROM Base'

Upvotes: 0

Rajesh
Rajesh

Reputation: 1620

Your Query is executing until

EXEC (@Base)

You are getting the

There is already an object named '##temp' in the database.

at

SELECT * FROM   ##temp

means you got error somewhere and it didn't reached the point

drop table ##temp

as @Ganesh_Devlekar mentioned Try adding

if  (object_ID('tempdb..##temp')) is not NULL
DROP TABLE ##temp

at the Starting

Upvotes: 1

Related Questions