Solved

stored procedure error: Conversion failed when converting the nvarchar value

Posted on 2007-04-03
6
3,022 Views
Last Modified: 2012-06-21
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
Comment
Question by:alivemedia
  • 3
  • 2
6 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18843965
>>-- Execute the SQL query
EXEC sp_executesql @sql<<

Can you use this instead?

Exec (@sql)
0
 
LVL 2

Author Comment

by:alivemedia
ID: 18843992
gives the same error
0
 
LVL 11

Accepted Solution

by:
dready earned 500 total points
ID: 18844027
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18844030
Throw in a

Print @SQL

to see what is getting into that variable
0
 
LVL 2

Author Comment

by:alivemedia
ID: 18844066
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18844086
nice catch dready!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now