• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3120
  • Last Modified:

stored procedure error: Conversion failed when converting the nvarchar value

I have the following SP:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [elevenboutique].[CategorySelectByClassID]
(
      @sortExpression nvarchar(50),
      @startRowIndex INT,
      @maximumRows INT,
      @ClassID INT
)
AS
IF LEN(@sortExpression) = 0
SET @sortExpression = 'CategoryName'

-- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment
SET @startRowIndex = @startRowIndex + 1
DECLARE @sql nvarchar(4000)

SET @sql ='SELECT  *
       FROM
(SELECT        *,
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
           FROM Category
WHERE        (ClassID = ' + CAST(@ClassID AS INT) + '))
AS Category
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
       ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                              + CONVERT(nvarchar(10), @maximumRows) + ') - 1'

-- Execute the SQL query
EXEC sp_executesql @sql

When I try to execute it with the following: EXEC CategorySelectByClassID 'CategoryName',0,100,305

I get the following error:
Msg 245, Level 16, State 1, Procedure CategorySelectByClassID, Line 16
Conversion failed when converting the nvarchar value 'SELECT  *
       FROM
(SELECT        *,
ROW_NUMBER() OVER(ORDER BY CategoryName) as RowNum
           FROM Category
WHERE        (ClassID = ' to data type int.

If I simply try to run this code I get no errors:
SELECT  *
       FROM
(SELECT        *,
ROW_NUMBER() OVER(ORDER BY CategoryName) as RowNum
           FROM Category
WHERE        (ClassID = 305))
AS Category
0
alivemedia
Asked:
alivemedia
  • 3
  • 2
1 Solution
 
Kevin3NFCommented:
>>-- Execute the SQL query
EXEC sp_executesql @sql<<

Can you use this instead?

Exec (@sql)
0
 
alivemediaAuthor Commented:
gives the same error
0
 
dreadyCommented:
The problem is in this code part i think:
SET @sql ='SELECT  *
       FROM
(SELECT        *,
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
           FROM Category
WHERE        (ClassID = ' + CAST(@ClassID AS INT) + '))


instead of casting the @classID to INT (what it allready is), you should cast it to varchar(15) or so (I do not know how long it can be). So replace that with

WHERE        (ClassID = ' + CAST(@ClassID AS varchar(15)) + '))

and i guess it should be OK

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Kevin3NFCommented:
Throw in a

Print @SQL

to see what is getting into that variable
0
 
alivemediaAuthor Commented:
huh, I assumed since the ClassID column was an integer I needed to cast it as that but since i am acutally just adding it to a string I needed to cast it as a varchar

makes sense - never would have though of that - thanks!!!!
0
 
Kevin3NFCommented:
nice catch dready!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now