CODEWITHSUNDEEP
sql-server-2008stored-procedures
user1783363
user1783363

Reputation: 13

Converting the nvarchar to int

I have google online this question and for the life of me couldn't figure out why this kept throwing errors, any help is appreciated.

Conversion failed when converting the nvarchar value 'SELECT @orderid = t.orderid FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY creationdatetime DESC) AS row FROM [intranet].[dbo].handbook_appendix_data WHERE id = ' to data type int.

DECLARE @sql nvarchar(500)
DECLARE @table varchar(40)
SET @table = 'handbook_section_data';
SET @table = 'handbook_appendix_data';

DECLARE @parentid INT
DECLARE @id INT
DECLARE @published INT
DECLARE @orderid INT
SET @parentid = 2
SET @id = -1
SET @published = 1

SET @sql = N'SELECT @orderid = t.orderid FROM 
(SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY creationdatetime DESC) AS row FROM [intranet].[dbo].'+@table+' WHERE id = '+@id+' AND published = '+@published+') AS t
WHERE t.published = '+@published+' AND t.row = 1';
EXEC SP_EXECUTESQL @sql, N'@orderid INT OUTPUT', @orderid = @orderid OUTPUT --Get the OrderID from ID

PRINT @orderid

Upvotes: 1

Views: 5472

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

WHERE t.published = '+@published+' AND t.row = 1'

@published is an int. SQL Server has decided that + must be between two ints, and is trying to convert the string to an int.

To stop this, force @published to be a string instead - then it's adding two strings (concatenation) instead:

WHERE t.published = '+CONVERT(varchar(10),@published)+' AND t.row = 1'

Upvotes: 1

Alex K.
Alex K.

Reputation: 175776

You need to cast the integer variables to a character type:

 ....
 WHERE id =' + cast(@id as varchar(32)) + ' AND published = ' + cast(@published as varchar(32)) + ') AS t ...

Upvotes: 2

podiluska
podiluska

Reputation: 51494

You need to convert your @id and @published ints to a varchar

 convert(varchar(50),@published)

ie 

SET @sql = N'SELECT @orderid = t.orderid FROM 
(SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY creationdatetime DESC) AS row FROM [intranet].[dbo].'+@table+' WHERE id = '+     convert(varchar(50),@id) +' AND published = '+     convert(varchar(50),@published) +') AS t
WHERE t.published = '+convert(varchar(50), @published) +' AND t.row = 1';

A better solution would be to avoid the string concatenation, and pass the @id and @published parameters using the sp_executesql as you are already doing with @orderid, or to question the requirement for dynamic SQL at all?

Upvotes: 4

Related Questions