Unfortunately building an 'in' statement in a stored procedure is not as
straight forward as one would like. You can not simply pass in a list and
SQL know what you are trying to do. You can, however, build the SQL
statement and then call the spExecuteSQL to run the new SQL
--------------------------------------------------------------------------
CREATE PROC myStoredProc @myString varchar100
AS
DECLARE @SQLStatement
SET @SQLStatement = 'select * from country where country_id in (' &
@myString & ')'
EXEC spExecuteSQL @SQLStatement
---------------------------------------------------------------------------
Check Books online to verify the syntax.
Mike Bruce
----- Original Message -----
From: "Jerry Staple" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, April 16, 2002 6:52 AM
Subject: RE: Stored Process
> Adrian,
> Im not sure as I am new to stored procedures.What do you think it should
> be ?
>
>
> -----Original Message-----
> From: Adrian Lynch [mailto:[EMAIL PROTECTED]]
> Sent: 16 April 2002 11:43
> To: CF-Talk
> Subject: RE: Stored Process
>
>
> cfsqltype="CF_SQL_INTEGER" <<<<<<<< is that correct??
>
> -----Original Message-----
> From: Jerry Staple [mailto:[EMAIL PROTECTED]]
> Sent: 16 April 2002 11:31
> To: CF-Talk
> Subject: Stored Process
>
>
> Hi ,
> I am transferring few queries into stored procedures but I have
> come across a stumbling block, and would be grateful if anyone could
> advise, for example:
>
> I am gathering a list of all countries on one page and sending them to
> the action page as a list, what I have is a query select * from country
> where country_id in (#url.countries#). This works fine in cfquery of
> course, but when I put it into a stored procedure it doesn't like the
> list of id's being passed in and only expects one value.
>
> I.E.
>
> <cfstoredproc
> procedure="sp_countries"
> datasource="geographical"
> RETURNCODE="YES"
> >
> <cfprocparam type="In"
> dbvarname="@country"
> value="#url.countries#"
> cfsqltype="CF_SQL_INTEGER">
>
>
> <cfprocresult name="Sp_Countries">
>
> </cfstoredproc>
>
>
>
> You may be thinking what would I want to put this in a stored procedure
> for, but above is only an example but the principle is what im after.
> How do you pass a list into a stored procedure.
>
> Many Thanks In advance
>
> Jerry Staple
> Web Application Developer
> Certified Coldfusion (5.0) Developer
>
>
> Head Office
> 133-137 Lisburn Road, Belfast
> Northern Ireland BT9 7AG
> T +44 (0) 28 9022 3224
> F +44 (0) 28 9022 3223
> E [EMAIL PROTECTED]
> W www.biznet-solutions.com
>
>
>
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
