Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 17th, 2004, 09:08 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 218
Thanks: 0
Thanked 0 Times in 0 Posts
Default DataType Conversion using WHERE IN ( )

I am getting a "Syntax error converting the varchar value '10,90' to a column of data type int." error when I run the following procedure:

@myList varchar(200)

Code:
SELECT column1
FROM table1
WHERE table1.ID IN (@myList)
When @myList is a single value, I get no errors. However, when @myList is a comma separated list like in the message above, I error out. I am using SQL Server 2000.

How else can I build this list of IDs? Thank you in advance for your comments.

--Colonel

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.
 
Old August 17th, 2004, 11:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

I had problems with that too... Execute it as dynamic SQL:

declare @SQL varchar(8000)
set @SQL = 'SELECT column1
FROM table1
WHERE table1.ID IN ' + @myList

exec @SQL

Brian
 
Old August 17th, 2004, 02:05 PM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 218
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Brian-

I was able to find another solution. What was going on was that SQL Server adds single quotes around the varchar parameter. I got around this by using the REPLACE function in the WHERE clause. My WHERE clause now looks like this:

WHERE table1.ID IN (REPLACE(@myList,'''',NULL))

I asked around, and of course your solution works, but I would lose some of the benefits of a stored procedure by using dynamic SQL. Specifically, the compiled execution plan.

Thank you for your comments.



- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.
 
Old August 18th, 2004, 07:43 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 218
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Brian-

My apologies - my solution did not work, I thought it had but I was mistaken.

Dynamic is the way to go.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.





Similar Threads
Thread Thread Starter Forum Replies Last Post
datatype checking stolte XSLT 1 March 20th, 2008 06:45 PM
convert datatype stealthdevil Access VBA 3 August 15th, 2007 02:15 PM
datatype umeshtheone SQL Server 2000 2 May 16th, 2007 11:56 AM
How Could I use C APIs with different DataType?? 6cet6 VS.NET 2002/2003 0 March 24th, 2004 09:20 PM
datatype ngang SQL Language 1 November 18th, 2003 09:55 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.