Solved

"Syntax error converting the nvarchar value"...

Posted on 2004-09-03
4
708 Views
Last Modified: 2008-02-01
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
Comment
Question by:arabiafish
  • 2
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
paelo earned 500 total points
ID: 11975909
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
 

Author Comment

by:arabiafish
ID: 11975949
@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
 
LVL 9

Expert Comment

by:paelo
ID: 11976097
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
 

Author Comment

by:arabiafish
ID: 11976341
Thanks!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

831 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