[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 724
  • Last Modified:

"Syntax error converting the nvarchar value"...

Why does the following give me a syntax error "Syntax error converting the nvarchar value 'INSERT INTO PeopleAnimalTable VALUES ('11111112',' to a column of data type int."?


CREATE PROCEDURE Update2
AS
DECLARE
@animalid int,
@personid char(8),
@tbl sysname,
@sql varchar(8000)

SET @tbl = 'PeopleAnimalTable'
SET @personid = '11111112'
SET @animalid = 1

SET @sql =  'INSERT INTO ' + @tbl + ' VALUES (''' + @personid + ''',' + @animalid) + ')'
EXEC(@sql)
0
arabiafish
Asked:
arabiafish
  • 2
  • 2
1 Solution
 
paeloCommented:
Try this:

SET @sql =  'INSERT INTO ' + @tbl + ' VALUES (''' + @personid + ''',' + CONVERT(nvarchar,@animalid)) + ')'

T-SQL does not do a lot of implicit conversions so you need to convert numerical and other data types to varchar or nvarchar when adding them to a string such as that.

-Paul.
0
 
arabiafishAuthor Commented:
@animalid is stored as an int in the database. Won't this cause problems (foreign keys, something like that) even though it works, or does SQL Server convert it back from nvarchar to int automatically?
0
 
paeloCommented:
The only reason you have to convert it is because you're using dynamic sql (ie. the EXEC statement).  So it needs to be in alphanumeric format to within the SQL statement, but it will be inserted as an integer, no problem.  Just to clarify (and because I noticed a syntax problem), your SET statement should read:

SET @sql =  'INSERT INTO ' + @tbl + ' VALUES (''' + @personid + ''',' + CONVERT(nvarchar,@animalid) + ')'

which would make the actual statement executed:

INSERT INTO PeopleAnimalTable VALUES ('11111112',1)

Try it in QA and you won't have any problems.  The personid is encapsulated with single quotes which designates it as a string and the animalid is inserted as a number.

-Paul.
0
 
arabiafishAuthor Commented:
Thanks!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now