CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
sqlDev
sqlDev

Reputation: 5

Error in exec SQL Server query

I'm a beginner at SQL Server.

I write this query and pass to this table name and count number with @tblName and @count

DECLARE @Base nvarchar(200) 

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

SET @Base = 'WITH Base AS (SELECT [picName],[Address],ID, ROW_NUMBER() OVER (ORDER BY Id DESC) RN FROM'
                + Quotename(@tblName) + ' GROUP BY [picName],[Address],ID)
                SELECT * INTO ##temp FROM Base'

EXEC (@Base)

SELECT *
FROM ##temp

declare @command nvarchar(max)

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

exec (@command)

drop table ##temp

but I get this error

Conversion failed when converting the nvarchar value 'SELECT TOP 15 [picName],[Address],(SELECT TOP 1 COUNT(Id) FROM [Brochure]) as AllSampleCount FROM [Brochure]WHERE [Id] IN (SELECT TOP 15 Id From ##temp WHERE RN >' to data type int.

Upvotes: 0

Views: 200

Answers (3)

Andriy M
Andriy M

Reputation: 77677

In this fragment:

((QUOTENAME(@Count)-1)*15)

the argument of QUOTENAME is @Count but my guess is the actual argument was supposed be (@Count-1)*15. The problem is how the brackets are placed. The -1 (and later *15) is logically applied to the result of QUOTENAME rather than to @Count. That causes the entire concatenation expression to be treated as numeric and SQL Server, therefore, tries to convert all the arguments in it to numbers. It fails to convert the very first one, 'SELECT TOP 15 ...', and that is what the error is about.

If you rewrite the above QUOTENAME fragment like this:

QUOTENAME((@Count-1)*15)

the issue will be gone.

Note that QUOTENAME expects a string and your argument is numeric. In this situation you can either convert the numeric expression's result to a string explicitly (using CAST or CONVERT), as others have suggested, or leave it without explicit conversion, as above – it will be converted automatically.

Upvotes: 0

Dudi Konfino
Dudi Konfino

Reputation: 1136

COUNT IS NUMBER SO CONVERTION TO STR IS NEEDED

QUOTENAME(CONVERT(NVARCHAR(10),@Count-1)*15  ) + 'ORDER BY Id DESC) ORDER BY Id DESC'

Upvotes: 0

Dgan
Dgan

Reputation: 10285

replace this:

You need to Convert @Count to nvarchar(20)

 set @command='SELECT TOP 15 [picName],[Address],(SELECT TOP 1 COUNT(Id) FROM ' + QUOTENAME(@tblName) + ') as AllSampleCount FROM ' + QUOTENAME(@tblName) +
             'WHERE [Id] IN (SELECT TOP 15 Id From ##temp WHERE RN >'+ 
             QUOTENAME(CAST((@Count-1)*15 as NVARCHAR(20))) + ' ORDER BY Id DESC) ORDER BY Id DESC'

Upvotes: 0

Related Questions