Link to home
Start Free TrialLog in
Avatar of alivemedia
alivemedia

asked on

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
Avatar of Kevin Hill
Kevin Hill
Flag of United States of America image

>>-- Execute the SQL query
EXEC sp_executesql @sql<<

Can you use this instead?

Exec (@sql)
Avatar of alivemedia
alivemedia

ASKER

gives the same error
ASKER CERTIFIED SOLUTION
Avatar of dready
dready

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Throw in a

Print @SQL

to see what is getting into that variable
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!!!!
nice catch dready!