check everywhere a datatype is declared, DB, CF stored proc, the SP...... it's usually somewhere, just check the lot
-----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 > > > > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com 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
