Solved

"Syntax error converting the nvarchar value"...

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 21
Sql server function help 15 26
What's wrong with this T-SQL Foreign Key? 7 39
SQL - Copy data from one database to another 6 18
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

815 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

12 Experts available now in Live!

Get 1:1 Help Now