Try using execute(@thestatement)
-----Original Message-----
From: Ryan Edgar [mailto:[EMAIL PROTECTED]]
Sent: 16 April 2002 15:31
To: CF-Talk
Subject: RE:Stored Procedure problem (was Stored Process)
I am working alongside Jerry on this and we are still running into
problems.
The Stored Proc is something like this:
CREATE PROCEDURE Sp_MyProc
DECLARE @mylist varchar(50), @thestatement varchar(255)
SET @mylist = '1607,1627,1647,1667'
SET @thestatement = 'SELECT my_id
FROM my_table
WHERE my_table.my_id IN (' +
@mylist + ')
ORDER BY my_table.my_id'
EXEC Sp_MyProc@thestatement
The error I get is "[ODBC SQL Server Driver][SQL Server]Error converting
data type varchar to int"
I cant declare @mylist as an int datatype as it is a string, but the
database seems to expect an int datatype to be passed.
If I set @mylist = 1607 and as an int datatype, I get the following
error: "Syntax error converting the varchar value 'SELECT
tables_colVScell.tables_cell_id FROM tables_colVScell, tables_rows WHERE
tables_colVScell.tables_row_id IN (' to a column of data type int"
Any help on this matter would be greatly appreciated as we are fairly
stumped.
TIA
Ryan Edgar
-----Original Message-----
From: Jerry Staple
Sent: 16 April 2002 12:12
To: CF-Talk
Subject: RE: Stored Process
Thanks very Much Mike
-----Original Message-----
From: Mike Bruce [mailto:[EMAIL PROTECTED]]
Sent: 16 April 2002 12:07
To: CF-Talk
Subject: Re: Stored Process
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
