Solved

stored procedure error: Conversion failed when converting the nvarchar value

Posted on 2007-04-03
6
3,056 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

734 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