Hi again,
OK, in our current application I,m turning all our queries to stored procs,
and its starting to feel like maybe I've bitten off too much, as my
knowledge of t-sql is one week, I've ordered a book by Garth Wells, 'T-SQL
programming with Stored Procedures', waiting for it to arrive.
OK so this what I am trying to do,
<CFSTOREDPROC
PROCEDURE="p_checkMV"
DATASOURCE="#REQUEST.dsn#"
USERNAME="#REQUEST.user#"
PASSWORD="#REQUEST.key#">
<CFPROCRESULT NAME="q_checkMV">
<CFPROCPARAM DBVARNAME="@customerID"
VALUE="#session.customerid#" CFSQLTYPE="CF_SQL_INTEGER" TYPE="IN">
<CFPROCPARAM DBVARNAME="@cfID" VALUE="#client.cfid#"
CFSQLTYPE="CF_SQL_CHAR" TYPE="IN">
<CFPROCPARAM DBVARNAME="@cftoken" VALUE="#client.cftoken#"
CFSQLTYPE="CF_SQL_CHAR" TYPE="IN">
<CFPROCPARAM DBVARNAME="@vehicleList"
VALUE="#ATTRIBUTES.selectedItems#" CFSQLTYPE="CF_SQL_VARCHAR" TYPE="IN">
</CFSTOREDPROC>
emphasis on
" <CFPROCPARAM DBVARNAME="@vehicleList" VALUE="#ATTRIBUTES.selectedItems#"
CFSQLTYPE="CF_SQL_VARCHAR" TYPE="IN"> "
which has a value of,
"
49,X504UTU,X504UTU|50,H7WPT,H7WPT|48,J8WPT,J8WPT|51,ECZ6771,ECZ6771|83,S163T
JA,S163TJA "
now in normal sql this is what I WAS doing,
<CFQUERY ...>
DELETE
FROM DBO.vehicleSelection
WHERE customerid=#session.customerid#
AND cfid=#client.cfid#
AND cftoken=#client.cftoken#
</CFQUERY>
<CFQUERY ...>
<CFLOOP FROM="1" TO="#listlen(ATTRIBUTES.selectedItems, '|')#"
INDEX="i">
<CFIF NOT listWithinList IS "">
<CFSET listWithinList="">
</CFIF>
<CFSET listWithinList=LISTGETAT(ATTRIBUTES.selectedItems, i,
"|")>
INSERT INTO dbo.vehicleSelection
(customerID,vehicleID,callsign,registration,cfid,cftoken)
VALUES (#session.customerID#,
#LISTGETAT(listWithinList, 1, ",")#,
'#LISTGETAT(listWithinList, 2,
",")#',
'#LISTGETAT(listWithinList, 3,
",")#',
#CLIENT.cfid#,
#CLIENT.cftoken#
</CFLOOP>
</CFQUERY>
So in my proc
CREATE PROCEDURE p_checkMV
(
@customerID int,
@cfid int,
@cftoken int,
@vehicleList VARCHAR
)
AS
DELETE
FROM vehicleSelection
WHERE customerid=@customerid
AND cfid=@cfid
AND cftoken=@cftoken
what I do next that would replicate my second cfquery I do not have the
foggiest,
I'm not sure about what conditional operators I need to use,
and what functions are avialable to me to manipulate @vehicleList,
Thanks for any help on this.
jmc
..
______________________________________________________________________
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
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
