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].[Category SelectByCl assID]
(
@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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [elevenboutique].[Category
(
@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
ASKER
gives the same error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Throw in a
Print @SQL
to see what is getting into that variable
Print @SQL
to see what is getting into that variable
ASKER
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!!!!
makes sense - never would have though of that - thanks!!!!
nice catch dready!
EXEC sp_executesql @sql<<
Can you use this instead?
Exec (@sql)