> Is there a way to tell SQL Server that the string you are
> passing it in a stored procedure should be evaluated as a
> list of numbers, not a string? I'm passing it a an HTML
> form field list.
>
> Here's a test I did for the stored procedure:
>
> declare @mylist varchar(50)
> set @mylist = '34,35,36,37'
>
> select * from focusareas
> where focusareaid IN (@mylist)
>
> the @mylist variable is actually going to be my form field
> list which will have the values. Problem is, SQL sees it as
> ONE String value, not a list of values and gives me this error:
>
> Syntax error converting the varchar value '34,35,36,37' to a
> column of data type int.
>
> I've tried just about everything. Even if I make double quotes
> around all the numbers, it still treats the whole thing as one
> string.
To the best of my knowledge, you can't do this. What you can do to get the
same effect, though, is to execute a string containing your SQL statement
(note that this example is SQL Server-specific):
CREATE PROC myproc
@mylist VARCHAR(50)
AS
sp_executesql("SELECT * FROM FOCUSAREAS WHERE FOCUSAREAID IN (" + @mylist +
")")
Of course, this isn't an optimal solution.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
