Solved

"Syntax error converting the nvarchar value"...

Posted on 2004-09-03
4
703 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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now