Yet another Conversion failed when converting the varchar value 'n' to data type int

Question

I have the following stored procedure:

CREATE PROCEDURE [cafgAddCoreID]
@HoldingName nvarchar (50) = null
@CountStart int = 0,
AS
DECLARE @sql nvarchar (50)
DECLARE @bit nvarchar (200)
BEGIN
    SET @bit = 'MTR1- + CAST ((' + @CountStart + ' + ROW_NUMBER() 
      OVER (ORDER BY [FindNo])) AS NVARCHAR(10))'
    SET @sql = 'INSERT INTO ' + @HoldingName + '([CoreID]) 
      SELECT (' + @bit + ') FROM [DectectoristMetalFinds]'
    EXEC @sql
END

But when I run:

EXEC [cafgAddCoreID] 'Table1', 9

I get

Conversion failed when converting the varchar value 'MTR1- + CAST ((' to data type int.

Running

INSERT INTO [Table1] ([CoreID])   
SELECT ('MTR1-' + CAST ((0 + ROW_NUMBER() OVER (ORDER BY FindNo)) 
  AS NVARCHAR(10))) AS CoreID FROM [Table2]

works so I know the method is right, but obviously not in an stored procedure.

Have you investigated the value of @sql before you exec it? — bendataclear
– bendataclear, Commented Mar 25, 2013 at 17:07
You need to make sure that MTR1- is surrounded by the appropriate number of apostrophes. Also, heeeeello SQL injection attacks. — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Mar 25, 2013 at 17:08
Asked so many times... possible duplicate of Conversion failed when converting the nvarchar value 'SELECT * FROM — Aaron Bertrand
– Aaron Bertrand, Commented Mar 25, 2013 at 17:12
Sorry, I am not sure how I can check the value of @sql as I am new to stored procedures. — Peter C
– Peter C, Commented Mar 25, 2013 at 17:12
Try PRINT @sql; but based on the error you won't get that far. You need to case your @CountStart as a string in order to build the string. — Aaron Bertrand
– Aaron Bertrand, Commented Mar 25, 2013 at 17:13

Aaron Bertrand · Accepted Answer · 2013-03-25 17:35:02Z

3

You can't "add" an integer to a string. Data type precedence will try to convert the surrounding literal to an int. Instead you need to explicitly change your int to a string:

SET @bit = '''MTR1-'' + CAST ((' + CONVERT(VARCHAR(12), @CountStart) + '...

That still doesn't look like it will parse right, but should get you to a point where you can investigate further by issuing:

PRINT @sql;

Instead of:

EXEC @sql;

Also you'll want to either use:

EXEC(@sql);

Or better yet:

EXEC sp_executesql @sql;

(Which you can use to parameterize @CountStart and avoid one vector for SQL injection. I'd write that version for you but it's impossible to tell from your question what valid SQL you're trying to produce in the first place.)

You might also want to consider giving more than 50 characters to @sql. Just a thought.

edited Mar 25, 2013 at 17:35

answered Mar 25, 2013 at 17:08

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Peter C Over a year ago

I know varieties of this have been asked, but I could not find an answer relevant to my particular issue. The sql I am trying to get to is: INSERT INTO [Table1] ([CoreID]) SELECT ('MTR1-' + CAST ((0 + ROW_NUMBER() OVER (ORDER BY FindNo)) AS NVARCHAR(10))) AS CoreID FROM [Table2]

Peter C Over a year ago

Sorry, I tried to add it as code but failed, I'll try again.

INSERT INTO [Table1] ([CoreID])    SELECT ('MTR1-' + CAST ((0 + ROW_NUMBER() OVER (ORDER BY FindNo))    AS NVARCHAR(10))) AS CoreID FROM [Table2]

This works.

Peter C Over a year ago

Thanks @aaron-bertrand. WIth the print @sql I found the problem. It was not enough '' around the MTR- and the convert of the @CountStart

Collectives™ on Stack Overflow

Yet another Conversion failed when converting the varchar value 'n' to data type int

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related