I gather this would/is a temp table?
-----Original Message-----
From: Ben Lowndes [mailto:[EMAIL PROTECTED]]
Sent: 15 January 2003 11:25
To: [EMAIL PROTECTED]
Subject: RE: [ cf-dev ] SQL question
You could use a sql funtion to turn the list into a table and call the
function:
CREATE FUNCTION dbo.fn_List2Table_int (@List varchar(8000))
RETURNS @List_tbl table(ID int)
AS
BEGIN
DECLARE @pos int
SET @List = @List + ','
SET @pos = CHARINDEX(',', @List)
WHILE @pos > 0 AND @pos IS NOT NULL
BEGIN
INSERT INTO @List_tbl(ID) VALUES
(CONVERT(int,LEFT(@List,@pos - 1)))
SET @List = RIGHT(@List, LEN(@List) - @pos)
SET @pos = CHARINDEX(',', @List)
END
RETURN
END
Then in your SP:
SELECT *
FROM tbl
WHERE myvar = (SELECT ID FROM fn_List2Table_int(@lstVar))
Ben
> -----Original Message-----
> From: Paul Johnston [mailto:[EMAIL PROTECTED]]
> Sent: 15 January 2003 10:48
> To: [EMAIL PROTECTED]
> Subject: [ cf-dev ] SQL question
>
>
> I'm a bit brain dead on the old SQL today (SQL Server 2000)
>
> I have a list like this:
>
> 1,2,3,4,5
>
> And I want to pass it into a stored procedure like this:
>
> <cfstoredproc procedure="blah"
> datasource="#request.datasource#" returncode="Yes">
> <cfprocparam type="In"
> cfsqltype="CF_SQL_VARCHAR" value="#mylist#">
> <cfprocresult name="rstblah" resultset="1">
> </cfstoredproc>
>
> The query looks something like this:
>
> SELECT *
> FROM tbl
> WHERE myvar IN ( @lstVar )
>
> With only one variable it works fine (cos it thinks "oh yeah,
> it's an int, so I'll change that!)
>
> However, with more than one variable, I have an error thrown
> saying that it doesn't like the fact I'm trying to change a
> varchar into an int (when I'm trying to make a varchar into a
> list of ints for use in the IN statement.
>
> Anyone?
>
> Paul
>
> -------------------------------------------
> Paul Johnston
> PJ Net Solutions Ltd
> http://www.pjnetsolutions.com
> [EMAIL PROTECTED]
> +44 (0)7866 573013
>
>
>
>
> --
> ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED] For human help, e-mail:
> [EMAIL PROTECTED]
>
>
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
