?
Solved

stored procedure error: Conversion failed when converting the nvarchar value

Posted on 2007-04-03
6
Medium Priority
?
3,080 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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