Data Type Conversion using WHERE IN ()

Colonel Angus · August 17th, 2004, 10:16 AM

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.

ioates · August 17th, 2004, 10:23 AM

CREATE PROCEDURE dbo.letterStatus
     @award as tinyint
    ,@members as varchar(500)
AS
EXEC('SELECT * FROM awardHistory
    WHERE membID IN (' + @members + ') '
    + @letterClause +
    ' AND awardTypeFK = ' + @award)

Where members could = '1,2,3,4'. this works for me

Regards
Ian

Colonel Angus · August 17th, 2004, 02:02 PM

I was able to find the 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))

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

Colonel Angus · August 17th, 2004, 02:03 PM

Ian-

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.

ioates · August 18th, 2004, 02:11 AM

Yes, I agree the REPLACE method looks more efficient

Jeff Mason · August 18th, 2004, 05:57 AM

Quote:

quote:Originally posted by Colonel Angus
I was able to find the 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))

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

And, according to your sig, you actually did test this approach, and it works?

Really?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Colonel Angus · August 18th, 2004, 08:33 AM

Ya know...

I wrote that reply because I thought I ran that and it worked. It must have been another procedure. My apologies - my solution did not work, I thought it had but I was mistaken.

Dynamic is the way to go.

However, it does strike me as odd that SQL Server cannot handle this better. I would think that this scenario presents itself often. In a former life I worked in a ColdFusion environment and it was able to handle this more elegantly (it probably converted to a dynamic SQL behind the scenes).

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

Hal Levy · August 18th, 2004, 09:12 AM

Cold Fusion.. ELegant? Not..

You could do the same thing in ASP.NET as cold fusion- Create the SQL statement in the code and then pass it to the SQL Server (Using CFQUERY or ADO.NET to connect changes nothing).. However, NEITHER solution lets you pre-compile and get the benefit of that from SQL Server.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Colonel Angus · August 18th, 2004, 09:17 AM

That's why I was hoping to find a way around using dynamic SQL; I still can reduce some network traffic by implementing as a stored procedure, but as you said I cannot get a compiled execution plan. bummer.

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

Hal Levy · August 18th, 2004, 09:20 AM

Is there a limit to the number of entries in @mylist?

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee