Try this: CREATE TABLE #x (ID int) INSERT INTO #x (ID) VALUES(1607) INSERT INTO #x (ID) VALUES(1627) INSERT INTO #x (ID) VALUES(1647) INSERT INTO #x (ID) VALUES(1667)
SELECT my_id FROM my_table WHERE my_table.my_id IN (SELECT ID FROM #x) ORDER BY my_table.my_id DROP TABLE #x You can use many methods to automate the insert statements. HTH, Chris ---------------------------------------------- Original Message From: ""<[EMAIL PROTECTED]> Subject: RE: Stored Procedure problem (was Stored Process) Date: Tue, 16 Apr 2002 16:22:41 +0100 >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 >> >> >> >> > > > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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
