?
Solved

"Syntax error converting the nvarchar value"...

Posted on 2004-09-03
4
Medium Priority
?
719 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
paelo earned 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 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